LastChild simulation in MDX - Multiple hierarchies

Question

I have a SSAS cube in which there is a measure that needs to be allocated via a percentage located in another measure. I have all this set up as a Measure Expression in my "Equity Amount" measure and it works great.

My problem is that this "Equity Amount" measure is actually a snapshot so I would need it to aggregate using the LastChild function. It turns out that you cannot have a measure expression in a semi additive measure so i'm trying to fake the LastChild function in MDX.

I've seen a lot of examples everywhere on the web and all but none of them talk about having multiple hierarchies in the date dimension. I have both "calendar Year" and "Fiscal Year" hierarchies.

My MDX works for one hierarchy but as soon as I scope for the second hierarchy, the first one gets overwritten. I'm guessing I need to treat both hierarchies in a single statement but am having a real tough time getting it to work.

Here is my MDX for one hierarchy. Can anyone help modify it for multiple hierarchies or is there any other way to solve my problem ?

Scope([Measures].[Equity Value]);
    This = iif(isleaf([Calendar].[By Calendar Year].CurrentMember),
                [Measures].[Equity Value],
                ([Calendar].[By Calendar Year].CurrentMember.LastChild,[Measures].[Equity Value]));
End Scope;

ic3 ic3 · Accepted Answer · 2011-11-04T16:46:46

David,

1) You're using scope as a calculated member. You can get rid of your iif by declaring the scope working only in a sub-cube :

 scope ([Measures].[Equity Value],[Calendar].[By Calendar Year].levels(0).... )
  This = (the expression)

2) Not sure to understand your problem but a tuple with two members of two hierarchies of the same dimension can be null by construction: As an example your first day of the calendar (1/1/2010) and the first day of your fiscal calendar (e.g. 1/6/2020) are not the same day so actually null. Two hierarchies of a same dimension are only ways of representing the same coordindates (here days), you're doing an intersection by declaring a tuple.

Not sure I'm helping you...

LastChild simulation in MDX - Multiple hierarchies

2 Answers