CODEWITHSUNDEEP
ssasmdx
Chris
Chris

Reputation: 93

SSAS - Can you do MDX calculations based on dimension level of an attribute?

I'm trying to make a dynamic calculation based on the dimension. Say I have three dimension levels but using the same measure like Field1 / Field2 to get the average.

The same calculation would apply to each dimension; however I'm using different attributes at different levels to calculate the average.

Example: Field1 / Field2 is divided when using level1 attributes, dimension1 Field1 / Field2 is divided when using level2 attributes, dimension2 Field1 / Field2 is divided when using level3 attributes, dimension3

It's the exact same calculation but needs to be dynamic when calculating based on the different levels of: level1, level2, and level3 attributes, all potentially being in different dimensions.

I'm fairly new to MDX so any coding help would be appreciated!!

I have not tried anything as of yet because I need a code example of how to understand how to write the expression.

I've seen something like this but don't know if it's in the right ballpark:

 WITH MEMBER [Measures].[Booking Window Value] AS
 [PNR Details].[Booking Window].CURRENTMEMBER.MEMBER_VALUE
 MEMBER [Measures].[Avg Booking Window Value] AS
 AVG([PNR Details].[Booking Window].[Booking Window].MEMBERS,[Measures]. 
 [Booking Window       Value])
 SELECT
 [Measures].[Avg Booking Window Value] ON COLUMNS
 FROM
 [SalesAnalysis]

I expect the output to be the MDX expression equivalent to calcuating the attributes based on levels of dimensions.

Upvotes: 2

Views: 132

Answers (1)

vldmrrdjcc
vldmrrdjcc

Reputation: 2132

You can check for level like this:

WITH MEMBER [Measures].[Booking Window Value] AS
CASE WHEN
    [MyDimension].[MyHierarchy].CURRENTMEMBER.Level
IS
    [MyDimension].[MyHierarchy].[Level1Name]
THEN
//Calculation for Level1
111
WHEN
    [MyDimension].[MyHierarchy].CURRENTMEMBER.Level
IS
    [MyDimension].[MyHierarchy].[Level2Name]
THEN
 //Calculation for Level2
222
ELSE

333

END

Upvotes: 1

Related Questions