SSAS MDX Calculated Measure Based on Related Dimension Attribute Value

2
votes

I have a measure [Measures].[myMeasure] that I would like to create several derivatives of based on the related attribute values.
e.g. if the related [Location].[City].[City].Value = "Austin" then I want the new calculated measure to return the value of [Measures].[myMeasure], otherwise, I want the new calculated measure to return 0.

Also, I need the measure to aggregate correctly meaning sum all of the leaf level values to create a total.

The below works at the leaf level or as long as the current member is set to Austin...

Create Member CurrentCube.[Measures].[NewMeasure] as
iif(
[Location].[City].currentmember = [Location].[City].&[Austin], 
[Measures].[myMeasure], 
0
);

This has 2 problems.
1 - I don't always have [Location].[City] in context.
2. When multiple cities are selected this return 0.

I'm looking for a solution that would work regardless of whether the related dimension is in context and will roll up by summing the atomic values based on a formula similar to above.

To add more context consider a transaction table with an amount field. I want to convert that amount into measures such as payments, deposits, return, etc... based on the related account.

1
ssasmdx
Let's say myMeasure returns a value of 1 for both locations in Austin, what is the Austin aggregated total? 1 or 2? – GregGalloway
in CurrentCube.[Measures].[NewMeasure] you should use IS rather than = when comparing to a member – whytheq

1 Answers

1
votes

I don't know the answer but just a couple of general helpers:

1 You should use IS rather than = when comparing to a member
2 You should use null rather than 0 - 0/NULL are effecitvely the same but using 0 will slow things up a lot as the calculation will be fired many more times. (this might help with the second section of your question)

Create Member CurrentCube.[Measures].[NewMeasure] as
iif(
[Location].[City].currentmember IS [Location].[City].&[Austin], 
[Measures].[myMeasure], 
NULL
);