I am trying to use MDX to dynamically determine which hierarchy of the
time dimension is selected. I have a calculated member which is using
the last non-empty descendant of the [Time].[Calendar] dimension, but
I want it to work even if Fiscal is selected instead of Calendar.
Here is the code

SUM(Tail(Filter(Descendants([Time].[Calendar].CurrentMember, [Calendar
Date]), isEmpty([Measures].[Employee Count]) = False), 1),
[Measures].[Employee Count])


What can I put in place of [Time].[Calendar].CurrentMember to take the
current hierarchy of the time dimension instead of the Calendar
hierarchy? Thanks!

Assuming you've got an all member in the two heirarchies so your calendars
don't interfere with each other, try this:

iif (not Time.Calendar.currentmember is time.calendar.defaultmember),

SUM(Tail(Filter(Descendants([Time].[Calendar].CurrentMember, [Calendar
Date]), isEmpty([Measures].[Employee Count]) = False), 1),
[Measures].[Employee Count])

,

SUM(Tail(Filter(Descendants([Time].[Fiscal].CurrentMember, [Fiscal
Date]), isEmpty([Measures].[Employee Count]) = False), 1),
[Measures].[Employee Count])

)

You'll need to tweek this if both the currentmembers are the defaultmembers
or if neither are, but the approach should work.

Hope this helps,
Richard


--
This posting is provided 'AS IS' with no warranties, and confers no rights.


"Sara" <sara2thebradley (AT) yahoo (DOT) com> wrote