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

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                     Gran开发者_开发百科dChild1
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

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.

I see you have posted this question here also (saw it originally on ssas msdn forum), so I am providing the link to my answer as it might help other people. thread link on SSAS msdn forum

@Marc - As this is a case of parent child dimension and p/c dimensions can have data associated on nonleaf members your query would not return the correct results. It took me some time to figure out how to aggregate the correct results from children in this case and recommend you have a look at the link. Offtopic: good luck with your product, I hope I'll get the time to evaulate it one day :)

Regards, Hrvoje