Search code examples
ssasmdx

Test whether member exists in dimension

This should be simple, I just can't figure out how to do it.

I'm making a calculated measure, which uses the PrevMember in a time dimension. I'd like to show NULL if the [Time dimension].CurrentMember has no PrevMember.

I just can't figure out what to wrap around this member expression to say "does this member actually exist?". I'm not interested in whether a particular measure exists against this .PrevMember (i.e. EXISTING or EXISTS). I don't want to refer to the measures dimension - just test whether [Time dimension].CurrentMember.PrevMember returns any member at all within the dimension.

EDIT The calculated measure in question is

([ThisPeriod]-PriorPeriod)/PriorPeriod

so I want to catch the cases where there is no prior period (at every level of the time hierarchy) and force the result to 0 rather than getting some kind of (div/0?) error.

Solution

  • This produces 0 where the CurrentMember does not have a Previous Member, at all levels.

    WITH MEMBER [Measures].[ThisPeriod] AS 3
    MEMBER [Measures].[PriorPeriod] AS 2
    MEMBER [Measures].[NewMeasure] AS 
IIF([Date].[Calendar].CURRENTMEMBER.PREVMEMBER IS NULL,0,[Measures].[ThisPeriod] - [Measures].[PriorPeriod])
SELECT
    {[Measures].[ThisPeriod], [Measures].[PriorPeriod], [Measures].[NewMeasure]} ON COLUMNS
,   [Date].[Calendar].MEMBERS ON ROWS
FROM
    [Adventure Works]
    ;

    enter image description here