How to create a calculated member based on two measures and the hierarchy level of the queried dimension's current member?

Question

I have a cube which has

two measure members: [Measures].[Value] (integer) and [Measures].[EffectiveBelowLevel] (integer).
a dimension called [DimParentChild] with a ragged user hierarchy called [ParentChildHierarchy].

I would like to create a calculated member on the measures dimension ([Measures].[EffectiveValue]) based on [Measures].[Value] which when queried along [DimParentChild] and [ParentChildHierarchy] behaves as follows:

- [Measures].[Value] is used if the hierarchy level of [DimParentChild].[ParentChildHierarchy].CURRENTMEMBER > [Measures].[EffectiveBelowLevel].
 - 0 is used if the hierarchy level of [DimParentChild].[ParentChildHierarchy].CURRENTMEMBER <= [Measures].[EffectiveBelowLevel].

Is it possible to achieve this functionaly with a calcuated member on the measures dimension?

If yes then what the formula would look like?

If not then what other way would there be? I am very interested in any other kind of solution as well (e.g. an mdx query, etc.)

As an example:

[Measures]
[Value] [EffectiveBelowLevel] ParentChildAssociation
10      1                     GrandChild1
20      2                     GrandChild2

[DimParentChild].[ParentChildHierarchy]
Member      HierarchyLevel   Description
Parent        1             - 
Child         2             first child of Parent 
GrandChild1   3             first child of Child
GrandChild2   3             second child of Child

With this data [Measures].[EffectiveValue] should look like this

ParentChild   EffectiveValue
Parent            0
Child            10
GrandChild1      10
GrandChild2      20

Marc Polizzi Marc Polizzi · Accepted Answer · 2011-04-02T09:01:10

How about something along the lines (I'm not sure about level ordinal being 0-based):

with member xx as 
  Sum( [DimParentChild].[ParentChildHierarchy].currentMember as myCurrentMember,
    Sum( Descendants( myCurrentMember(0), 64, LEAVES ), 
         IIF( myCurrentMember(0).level.ordinal > [EffectiveBelowLevel], [Value], 0 ) 
    )
  )

select [xx] on 0, [DimParentChild].[ParentChildHierarchy].members on 1 from [...]

You can have a look to this MDX documentation here for more details.

How to create a calculated member based on two measures and the hierarchy level of the queried dimension's current member?

2 Answers