MDX calculation for multiple measures

Question

I have to create the next calculation for 20 different Measures in my OLAP. As you can see the described function calculates the value of a measure the previous year. I have to repeat the code 20 times only changing the specific measure ([Measures].[Sales]) and I think there is a way of changing the specific measure dynamically.

create MEMBER CURRENTCUBE.[Measures].[Sales ant] as
(ParallelPeriod(
             [Dim Calendar].[Calendar].[Year]
            ,1
            ,[Dim Calendar].[Calendar].CurrentMember
            ), [Measures].[Sales]),
 VISIBLE = 1  ,ASSOCIATED_MEASURE_GROUP = 'Fact Sales';

Is there is a way in MDX to know which measure in the [Measures] group is being used? In this case which is the best way to implement it.

vldmrrdjcc vldmrrdjcc · Accepted Answer · 2020-09-03T13:26:13

If I remember correctly you can create hidden set of measures like this:

CREATE HIDDEN STATIC SET CURRENTCUBE.[My Set Of Measures] AS 
{
[Measures].[Measure 1],
[Measures].[Measure 2],
[Measures].[Measure 3]
};

Then you can create scope assignment for this set:

Scope ([My Set Of Measures]);
this = (ParallelPeriod(
             [Dim Calendar].[Calendar].[Year]
            ,1
            ,[Dim Calendar].[Calendar].CurrentMember
            ), Measures.CurrentMember)
End Scope;

Note that instead of concrete measure - [Measures].[Sales] you will reference a measure with Measures.CurrentMember, so when you choose [Measures].[Sales] on your report the scope above will apply.

Hope it will work.

MDX calculation for multiple measures

1 Answers