Cross join multiple measure groups in MDX

Question

I have two dimensions, and one measure group: [Time].[Day], [Scenario].[Scenario] and the measures group containing two measures, [Measures].[CleanPrice] and [Measures].[DirtyPrice]

I start out with the following result which works fine:

                   01-01-2011  01-01-2012
Base  CleanPrice      100         100
Base  DirtyPrice      100         100
Up1%  CleanPrice      101         101
Up1%  DirtyPrice      101         101

What I really want to produce is a calculated member to show the measure, and the delta between the current scenario and the Base scenario.

The desired result should look like the following:

                         01-01-2011  01-01-2012
Base  CleanPrice  Value    100         100
Base  CleanPrice  Delta    0           0
Base  DirtyPrice  Value    100         100
Base  DirtyPrice  Delta    0           0
Up1%  CleanPrice  Value    101         101
Up1%  CleanPrice  Delta    1           1
Up1%  DirtyPrice  Value    101         101
Up1%  DirtyPrice  Delta    1           1

I attempted something like this, but it is not possible to cross join measure groups:

WITH MEMBER [Value] as [Measures].CurrentMember
MEMBER [Delta] as [Measures].CurrentMember - ([Scenario].[Scenario].&[0], [Measures].CurrentMember)
SELECT NON EMPTY 
{ [Time].[Day].&[2011-01-01T00:00:00], [Time].[Day].&[2012-01-01T00:00:00] }
ON COLUMNS,
{ [Scenario].[Scenario].&[0], [Scenario].[Scenario].&[1] }  * 
{ [Measures].[CleanPrice], [Measures].[DirtyPrice] } *
{ [Value], [Delta] }
ON ROWS
FROM Results

I create two calculated members, [Value] to simply be the current measure, and [Delta] to take the current measure, and get the difference for that measure against the base scenario.

I would like to cross join the measure group that has the clean price and dirty price, with a measure group of the two calculated members, but this is not possible.

Is there any way to produce the desired result? I would like to avoid creating the measures [CleanPrice Delta] and [DirtyPrice Delta] because I actually have a lot of them.

Sam Holloway Sam Holloway · Accepted Answer · 2013-01-24T14:05:15

One approach would be to create a new dimension, say, [Metric] with two members, [Value] and [Delta]. Then write MDX into the calculation scripts along the following lines:

SCOPE ([Metric].[Delta], [Scenario].[Scenario].[Scenario]);
    this =   ([Metric].[Value], [Scenario].[Scenario].currentmember)
           - ([Metric].[Value], [Scenario].[Scenario].&[0]);
END SCOPE;

This will then automatically work across all measures that are linked to the [Scenario] dimension.

Cross join multiple measure groups in MDX

1 Answers