I am trying to create a "Calcaulated Member" for computing OPening Balance.
The cube is as follows:

Measures
Census Count (will always have a value of 1)

Dimensions
==========
Calendar (has the following attributes)
- Group ID
- Calendar ID (values are 19781103,19781104,19781105....i.e one record per
calendar date)
- attribute3
- attribute4

The MDX used for computing the opening balance is
SUM(FILTER(DESCENDANTS([Measures].Currentmember,[Census Count]),
([Calendar].[CALENDAR ID].Currentmember,[Census Count]).ITEM(0)>"19781103"))

I am always getting ZER0 (0) as the calculated value. Could anybody please
help as to what I could be doing wrong here?

Any help is greatly appreciated.
--
Thank you,
Ashok G

Hi Ashok,

Could you clarify a couple of things for your cube:

- What is the role of the Group ID and other attributes of the Calendar
dimension: any examples of the cube data?

- Why are you applying Descendants() to Measures:
DESCENDANTS([Measures].Currentmember,[Census Count])


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for responding.

THe GroupID field has no bearing on the calculation (I should not have added
it to the dimension info..my apologies for that); instead I had to filter out
NULL values from the "Profit Center Rollup" dimension as shown below. I have
got it working now and the Calculated Member (Opening Headcount) is
calculated as:

SUM(FILTER(DESCENDANTS([Profit Center Rollup].[Profit Center
Rollups].CurrentMember,[Profit Center Rollup].[Profit Center Rollups]),NOT
ISEMPTY([Profit Center Rollup].[Profit Center
Rollups].Members)),[Measures].[Census Count])


Thanks once again for getting me thinking.
--
Thank you,
Ashok G


"Deepak Puri" wrote: