MDX named set with non empty last child

Question

I have a "Trend" dimension. The hierarchy is, Trend->Week->Day. The fact is linked to the "Day" key. There is no guarantee that measure value will exist for all days in a given week.

When the user wants to see the measure at "Week" level, I need to show only the last empty day value for that Week. I have multiple measures and hence I ain't interested in creating a new calculated measure for each one of them (like How to display the total of a level as the value of its last child in MDX)

Instead, is there any way to create a named set with which I can achieve the functionality as below?

Example

Week    Day    Measure
1       1      4
1       2      5
2       3      7
2       7      9
3       5      10

Should get at "Week" level as

Week    Day    Measure
1       2      5
2       7      9
3       5      10

Thanks! :)

Marc Polizzi Marc Polizzi · Accepted Answer · 2012-09-27T09:05:36

If you want to create a calculated member that can be reused for several measures, you can create a utility dimension that will contain a bunch of calculated member only. Dunno how to do that in SSAS (I'm familiar with icCube). Then you can use this hierarchy in your requests to apply the calculated member.

Let's take your example. I've called the utility dimension [Stats]. Its default member is a calculated member returning the value of the current measure. And it contains the [Last Day] calc. member.

WITH MEMBER [Stats].[Stats].[Last Day] AS (
NonEmpty( 

   Order( [Trend].[Trend].currentMember.children, 
          [Trend].[Trend].currentMember.properties( 'key', TYPED ), 
          BDESC 
   ), 
   [Stats].[Stats].defaultMember 

).item(0), 

[Stats].[Stats].defaultMember  )
SELECT [Measures].members on 0, [Trend].[Trend].[Week].members on 1

FROM [your-cube]

WHERE [Stats].[Stats].[Last Month]

You can see the trick with [Last Month] in the slicer that is applied to each [MEasures] of the SELECT. Then its formula is using a NonEmpty of the [Days] (reversed with the order() based on the key - you might need to adjust) for the current [MEasures].

MDX named set with non empty last child

1 Answers