Answered by:
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 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
Question
Answers

Hi Zoltan,
You are right then.
What you could do is add a slight change to the previous calculation.
I'll try to provide an example using Product Category that will serve to simulate the EffectiveBellowLevel dimension. So lets change the EffectiveBellowMeasure to dynamically determine the level based on hard coded Joe Pack employee and 'Product Category' member key. As there are 4 product categories with member keys 14, rows associated with each of product categories and Joe Pack should be eliminated based on the level of "current" employee and product category. So "Accessories" with member key 4 should not aggregate to Joe Pack's parent Amy E. Alberts (level 3). Accessories (4) and Clothing (3) categories related to Joe Pack should be eliminated for Brian S. Welcker (level 2). And so on, All employees member (level 0) should have all categories (14) related to Joe Pack eliminated from it's aggregation.
WITH MEMBER [Measures].[P] AS Int([Product].[Product Categories].CURRENTMEMBER.MEMBER_KEY) MEMBER [Measures].[EffectiveBelowLevel] AS CASE [Employee].[Employees].CURRENTMEMBER  WHEN [Employee].[Employees].&[291] THEN 2 /* Jae B. Pak  Level 4 */ WHEN [Employee].[Employees].&[291] THEN [Measures].[P] ELSE 0 /* all other employees */ END MEMBER [Measures].[Employee Level] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL member [Measures].[Eliminate Desc] as Generate ( Filter ( existing [Product].[Product Categories].[Category], [Measures].[P] > [Measures].[Employee Level] ) ,[Product].[Product Categories].CurrentMember.NAME ,',' ) MEMBER [Measures].[Reseller Sales Amount Calc] AS SUM({[Employee].[Employees].CURRENTMEMBER} as CM, null) + SUM ( Filter ( {Descendants( [Employee].[Employees].CURRENTMEMBER)  [Employee].[Employees].[All Employees]} * existing [Product].[Product Categories].[Category] ,[Measures].[EffectiveBelowLevel] <= (CM.Item(0).Level.Ordinal) ) , ( [Employee].[Employees].CURRENTMEMBER.DataMember, [Product].[Product Categories].CurrentMember, [Measures].[Reseller Sales Amount] ) ), FORMAT_STRING = "Currency" SELECT { [Measures].[P], [Measures].[Eliminate Desc], [Measures].[Employee Level], [Measures].[EffectiveBelowLevel], [Measures].[Reseller Sales Amount], [Measures].[Reseller Sales Amount Calc] } ON COLUMNS, Hierarchize ( ASCENDANTS([Employee].[Employees].&[291]) +[Employee].[Employees].&[290].CHILDREN ) Descendants([Product].[Product Categories],1,SELF_AND_BEFORE) ON ROWS FROM [Adventure Works]
I hope this is more appropriate to your situation.
HTH,
Hrvoje Piasevoli
 Proposed as answer by Jerry NeeModerator Tuesday, April 05, 2011 8:48 AM
 Marked as answer by hadadiz Wednesday, April 06, 2011 11:35 AM
All replies

Have you tried CASE statement to accomplish this?
Check CASE statement in MDX here...
http://msdn.microsoft.com/enus/library/ms144841.aspx
Please visit my Blog for some easy and often used tsql scripts 
Hi,
Here is an example on Adventure Works cube:
Note that I'm creating a calculated measure here [Measures].[EffectiveBelowLevel] to simulate a situation where 3 employees from the [EmployeeSet] have the first case (level.Ordinal > [EffectiveBellowLevel]). If the condition has been satisfied the calculated measure [Measures].[Reseller Sales Amount Calc] will return the value of the actual measure [Measures].[Reseller Sales Amount]. Since you already have a measure group for that neither the set not that calculation would be needed.
with set [EmployeeSet] as { [Employee].[Employees].&[291],  Jae B. Pak 4 [Employee].[Employees].&[295],  Rachel B. Valdez 4 [Employee].[Employees].&[272] Stephen Y. Jiang 3 } member [Measures].[EffectiveBelowLevel] as Iif ( Intersect ( [Employee].[Employees].CurrentMember, {EmployeeSet} ).Count > 0 ,[Employee].[Employees].CurrentMember.Level.Ordinal  1 ,NULL ) member [Measures].[Employee Level] as [Employee].[Employees].CurrentMember.Level.Ordinal member [Measures].[UniqueName] as [Employee].[Employees].CurrentMember.UniqueName member [Measures].[Reseller Sales Amount Calc] as Iif ( NOT IsEmpty([Measures].[EffectiveBelowLevel]) AND [Measures].[Employee Level] > [Measures].[EffectiveBelowLevel] ,[Measures].[Reseller Sales Amount] ,0 ), Format_String = "Currency" select { [Measures].[Employee Level],  [Measures].[UniqueName], [Measures].[EffectiveBelowLevel], [Measures].[Reseller Sales Amount], [Measures].[Reseller Sales Amount Calc] } on 0, [Employee].[Employees].MEMBERS on 1 from [Adventure Works]
HTH,
Hrvoje Piasevoli

Hello,
Thanks for your suggestion, but I am afraid it does not do exactly what I would like to have.
I modified your query a little bit so I can explain maybe a bit better exactly what I would like to accomplish.
WITH MEMBER [Measures].[EffectiveBelowLevel] AS CASE [Employee].[Employees].CURRENTMEMBER WHEN [Employee].[Employees].&[291] THEN 2 /* Jae B. Pak  Level 4 */ ELSE 0 /* all other employees */ END MEMBER [Measures].[Employee Level] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL MEMBER [Measures].[Reseller Sales Amount Calc] AS IIF ( NOT ISEMPTY([Measures].[EffectiveBelowLevel]) AND [Measures].[Employee Level] > [Measures].[EffectiveBelowLevel] ,[Measures].[Reseller Sales Amount] ,0 ), FORMAT_STRING = "Currency" SELECT { [Measures].[Employee Level], [Measures].[EffectiveBelowLevel], [Measures].[Reseller Sales Amount], [Measures].[Reseller Sales Amount Calc] } ON COLUMNS, ASCENDANTS([Employee].[Employees].&[291]) ON ROWS FROM [Adventure Works]
The result of the query (only a specific employee and all of it's ascendants are included from employees):
Employee Level EffectiveBelowLevel Reseller Sales Amount Reseller Sales Amount Calc Jae B. Pak 4 2 $8,503,338.65 $8,503,338.65 Amy E. Alberts 3 0 $15,535,946.26 $15,535,946.26 Brian S. Welcker 2 0 $80,450,596.98 $80,450,596.98 Ken J. Sánchez 1 0 $80,450,596.98 $80,450,596.98 All Employees 0 0 $80,450,596.98 $0.00
What I would like to achieve:
Employee Level EffectiveBelowLevel Reseller Sales Amount Reseller Sales Amount Calc Jae B. Pak 4 2 $8,503,338.65 $8,503,338.65 Amy E. Alberts 3 0 $15,535,946.26 $15,535,946.26 Brian S. Welcker 2 0 $80,450,596.98 $71,947,258.33 Ken J. Sánchez 1 0 $80,450,596.98 71,947,258.33 All Employees 0 0 $80,450,596.98 $0.00
The [Reseller Sales Amount] and [Reseller Sales Amount Calc] for [Brian S. Welcker] and [Ken J. Sánchez] should be different.
They should be different as the calculated value from [Jae B. Pak] should not be included in the aggregation any more as it is told by [EffectiveBelowLevel] value 2 for [Jae B. Pak].
And there is a slight problem with having the [EffectiveBelowLevel] as a calculated member as this way it is possible to define for each already aggregated value. On the other hand when I have the [EffectiveBelowLevel] values on the measures they will be aggregated (summed up) when used in the query at this point.
Thank You really much for Your help!
Zoltan

Hello Atifullah Sheikh,
Thank you for your suggestion, I tried using IIF and CASE would give me similar functionality.
Unfortunately the problem problem seems to have complexities which can not be solved easily by any of these statements.
Kind Regards,
Zoltan

Hi,
OK it seems I've misunderstood your rules but I understand now you need to eliminate those mombers from the parent aggregation according to parent's level.
Here is a modified query where the amounts are eliminated but it has some tricks to reconstruct the sum as this is a parent child dimension where non leaf members have data.
I'l just paste the query and can explain it latter if it works for you as I have to go now.
Query
WITH MEMBER [Measures].[EffectiveBelowLevel] AS CASE [Employee].[Employees].CURRENTMEMBER WHEN [Employee].[Employees].&[291] THEN 2 /* Jae B. Pak  Level 4 */ ELSE 0 /* all other employees */ END MEMBER [Measures].[Employee Level] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL MEMBER [Measures].[Reseller Sales Amount Calc] AS SUM({[Employee].[Employees].CURRENTMEMBER} as CM, null) + SUM ( Filter ( Descendants( [Employee].[Employees].CURRENTMEMBER)  [Employee].[Employees].[All Employees] ,[Measures].[EffectiveBelowLevel] <= (CM.Item(0).Level.Ordinal) ) ,([Employee].[Employees].CURRENTMEMBER.DataMember, [Measures].[Reseller Sales Amount]) ), FORMAT_STRING = "Currency" SELECT { [Measures].[Employee Level], [Measures].[EffectiveBelowLevel], [Measures].[Reseller Sales Amount], [Measures].[Reseller Sales Amount Calc] } ON COLUMNS, Hierarchize ( ASCENDANTS([Employee].[Employees].&[291]) +[Employee].[Employees].&[290].CHILDREN ) ON ROWS FROM [Adventure Works]
HTH,
Hrvoje Piasevoli

And there is a slight problem with having the [EffectiveBelowLevel] as a calculated member as this way it is possible to define for each already aggregated value. On the other hand when I have the [EffectiveBelowLevel] values on the measures they will be aggregated (summed up) when used in the query at this point.
Hi, just a quick comment on the above. So [EffectiveBelowLevel] is a member property right? It should be simple to replace the expression in query with something like [Employee].[Employees].CurrentMember.Properties("EffectiveBelowLevel", TYPED)
Regards,
Hrvoje Piasevoli

Hello,
Thanks for the summing up mdx, it does what I wanted, except where [EffectiveBelowLevel] is taken from.
Regarding the "slight problem" above, in my actual cube I have two options for getting [EffectiveBelowLevel]:
 Initially I have [EffectiveBelowLevel] as a member property in an other dimension than the one I use for projecting (in case of above mdxes not in the [Employee] dimension). The question for the final solution would be if it is possible to use such a dimension member property in this kind of calculations?
 If the previous way is not viable, I have the possibility to store the [EffectiveBelowLevel] as a Measure in the same measure group as the value which has to be summed up (just fill it along the etl). But in this case how can I use it?  as it is aggregated for each of the leaf members of the [Employee] dimension.
Kind Regards,
Zoltan

Hi,
First I am glad that the aggregation is working now.
Regarding the [EffectiveBelowLevel]  I guess it would be possible to use it off of an another dimension but I'm not sure how the two are related so would need more info on that.
If there is a reason you can not have it as a member property of the parentchild dimension maybe it would be the easiest to have that value in a (small) separate measure group linked to the parentchild dimension.
HTH,
Hrvoje Piasevoli

Hello,
I try to describe the situation with EffectiveBelowLevel as I have in my real cube, using the initial example.
The star schema the cube is based on is made up of the following tables:
 DimParentChild table  defines the dimension with the parentchild hierarchy.
 DimEffectiveBelowLevel  defines the dimension for the effective below level values.
 FactValues  contains the Value column and FK columns pointing to DimParentChild and DimEffectiveBelowLevel.
There is no onetoone association between DimEffectiveBelowLevel and DimParentChild entries.
The parentchild and the effectivebelowlevel dimensions are related only through the entries in FactValues table.
There may be some [Measures].[Value] which belong to [DimParentChild].[ParentChildHierarchy].[NodeX] and have corresponding [DimEffectiveBelowLevel].[EffectiveBelowLevelHierarchy].[2] and [DimEffectiveBelowLevel].[EffectiveBelowLevelHierarchy].[5] respectively.I hope that this cleared up the circumstances.
Regarding the "... separate measure group ..." I do not see how this would work in this case.
It is possible that there is a problem already with the way I have the star schema  but in this case what would be the good way to model this situation?
Thanks for bearing with me ;)
Zoltan 
Hi Zoltan,
You are right then.
What you could do is add a slight change to the previous calculation.
I'll try to provide an example using Product Category that will serve to simulate the EffectiveBellowLevel dimension. So lets change the EffectiveBellowMeasure to dynamically determine the level based on hard coded Joe Pack employee and 'Product Category' member key. As there are 4 product categories with member keys 14, rows associated with each of product categories and Joe Pack should be eliminated based on the level of "current" employee and product category. So "Accessories" with member key 4 should not aggregate to Joe Pack's parent Amy E. Alberts (level 3). Accessories (4) and Clothing (3) categories related to Joe Pack should be eliminated for Brian S. Welcker (level 2). And so on, All employees member (level 0) should have all categories (14) related to Joe Pack eliminated from it's aggregation.
WITH MEMBER [Measures].[P] AS Int([Product].[Product Categories].CURRENTMEMBER.MEMBER_KEY) MEMBER [Measures].[EffectiveBelowLevel] AS CASE [Employee].[Employees].CURRENTMEMBER  WHEN [Employee].[Employees].&[291] THEN 2 /* Jae B. Pak  Level 4 */ WHEN [Employee].[Employees].&[291] THEN [Measures].[P] ELSE 0 /* all other employees */ END MEMBER [Measures].[Employee Level] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL member [Measures].[Eliminate Desc] as Generate ( Filter ( existing [Product].[Product Categories].[Category], [Measures].[P] > [Measures].[Employee Level] ) ,[Product].[Product Categories].CurrentMember.NAME ,',' ) MEMBER [Measures].[Reseller Sales Amount Calc] AS SUM({[Employee].[Employees].CURRENTMEMBER} as CM, null) + SUM ( Filter ( {Descendants( [Employee].[Employees].CURRENTMEMBER)  [Employee].[Employees].[All Employees]} * existing [Product].[Product Categories].[Category] ,[Measures].[EffectiveBelowLevel] <= (CM.Item(0).Level.Ordinal) ) , ( [Employee].[Employees].CURRENTMEMBER.DataMember, [Product].[Product Categories].CurrentMember, [Measures].[Reseller Sales Amount] ) ), FORMAT_STRING = "Currency" SELECT { [Measures].[P], [Measures].[Eliminate Desc], [Measures].[Employee Level], [Measures].[EffectiveBelowLevel], [Measures].[Reseller Sales Amount], [Measures].[Reseller Sales Amount Calc] } ON COLUMNS, Hierarchize ( ASCENDANTS([Employee].[Employees].&[291]) +[Employee].[Employees].&[290].CHILDREN ) Descendants([Product].[Product Categories],1,SELF_AND_BEFORE) ON ROWS FROM [Adventure Works]
I hope this is more appropriate to your situation.
HTH,
Hrvoje Piasevoli
 Proposed as answer by Jerry NeeModerator Tuesday, April 05, 2011 8:48 AM
 Marked as answer by hadadiz Wednesday, April 06, 2011 11:35 AM


Hi Zoltan,
It's great to hear that :) Your feedback and clear explanations have certainly contributed a lot.
PS:
Unfortunately the problem problem seems to have complexities which can not be solved easily by any of these statements.
Says who? ;)
Regards,
Hrvoje Piasevoli