CODEWITHSUNDEEP
ssasmdx
gwoody1984
gwoody1984

Reputation: 315

Get next member that matches criteria mdx

I am attempting to return a measure two periods from the current member on the time dimension however I need to only include periods that match a certain criteria (Is_Business_Day = true)

I currently have:

(
    [Date].[Calendar].CURRENTMEMBER.NEXTMEMBER.NEXTMEMBER, 
    [Measures].[SOME MEASURE]
)

Which accurately returns the value of the measure two members in the future, but now I need to additionally apply the filter, but can't quite figure out how to do so.

EDIT:

My thinking is that I would have to do something similar to the following

(
    Head(
        exists(
            [Date].[Calendar].CurrentMember.NextMember:[Date].[Calendar].CurrentMember.Lead(6),
            [Date].[Is Business Day].&[True]
        ),
        2
    ).item(1),
    [Measures].[SOME MEASURE]
)

Upvotes: 1

Views: 758

Answers (1)

FrankPl
FrankPl

Reputation: 13315

As both hierarchies, [Date].[Calendar] and [Date].[Is Business Day] are in teh same dimension, you can rely on SSAS "Autoexists" which is usually faster than Exists. Hence,

((([Date].[Calendar].[Date].&[20050718].nextmember : null )
  *
  { [Date]..Item(0) }
 ).Item(0)
 ,[Measures].[SOME MEASURE]
)

The : null construct builds a set to the end of the Date level, i. e. to the last day contained in the attribute.

Cross joining with [Date].[Is Business Day].&[True] automatically restricts the set to those members that co-exist in the dimension (the magic of autoexists).

And .Item(0) extracts the first tuple. In case you need a tuple not of the date, the .Item(0), and the measure member, but just of the date and the measure in your context, apply another Item(0) after the first. This would extract the first member from the tuple.

Upvotes: 1

Related Questions