Search code examples
ssas

SSAS : Calculated Member based on a condition in the same row

I have this fact table

enter image description here

I want to create a calculated member like this : S = the sum of the "TOT_LIV" that have the "NUM_PROD" = 1

For example:

enter image description here

S should be equal to 50

How could I do it ?

Solution

  • Assuming you have a measure defined on this fact table named "TOT LIV", this measure has "Sum" defined as its aggregate method, and that NUM_PROD is a foreign key to a dimension named Prod having an attribute named Num Prod which I assume being based on the primary key of the dimension, and hence it would be 1 as well for the records that are references by the NUM_PROD primary key, you would use

    ([Measures].[TOT LIV], [Prod].[Num Prod].[1])

    You see there is no 1:1 translation from SQL to MDX, a lot of things depend on cube setup, which predefines many behaviors that you have to repeat again and again in SQL queries (like the usage of sum).