Test whether member exists in dimension

1
votes

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.

2
ssasmdx
Perhaps test whether the CurrentMember is the first member in the dimension (if it is first it can't have a PrevMember). Or even if CurrentMember.PrevMember is null should do it. – The Dumb Radish

2 Answers

2
votes

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

1
votes

Not sure you need to do anything - if there is no previous member then null will just get returned anyhow?

WITH 
  MEMBER Measures.[CalendarPrevious] AS 
    [Date].Calendar.CurrentMember.PrevMember.Member_Caption 
  MEMBER Measures.[CalendarPrevious2] AS 
    IIF
    (
      Count({[Date].Calendar.CurrentMember.PrevMember}) = 1
     ,[Date].Calendar.CurrentMember.PrevMember.Member_Caption
     ,null
    ) 
SELECT 
  NON EMPTY 
    {
      Measures.[CalendarPrevious]
     ,Measures.[CalendarPrevious2]
    } ON 0
 ,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works];

Returns:

enter image description here

To combat div errors I generally use this pattern:

  MEMBER [Date].[Date - Calendar Month].[All].[DlyAvgGrowth] AS 
    IIF
    (
      [Date].[Date - Calendar Month].[All].[PrevMth_DlyAvg] = 0
     ,NULL
     ,Divide
      (
          [Date].[Date - Calendar Month].[All].[CurrentMth_DlyAvg]
        - 
          [Date].[Date - Calendar Month].[All].[PrevMth_DlyAvg]
       ,[Date].[Date - Calendar Month].[All].[PrevMth_DlyAvg]
      )
    )