Calculated Member based on dimension attribute value

user992228 picture user992228 · Oct 30, 2013 · Viewed 12.6k times · Source

I want to make some Calculated Measures in my cube that conditionally use a value from the measure table, based on a dimension attribute value.

For example: where Document Status in the Document dimension is CP, use the Committed Value measure. This is what I have for that:

CREATE MEMBER CURRENTCUBE.[Measures].CalcCommittedValue
 AS ([Document].[Document Status].&[CP], [Measures].[Committed Value]), 
FORMAT_STRING = "$#,##0;-$#,##0", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Document Total'  ;   

And it looks like it works, until I start browsing the cube and put Document Status and CalcCommittedValue into the Browser. All the options for Document Status display the same CalcCommittedValue.

Thanks for your help!

Answer

Josh Fennessy picture Josh Fennessy · Oct 30, 2013

To do this correctly, try using the SCOPE() statement...for example

CREATE MEMBER CURRENTCUBE.[Measures].[CalcCommittedValue]
AS

  SCOPE([Document].[Document Status].[&CP]);

   THIS = [Measures].[Committed Value];

  END SCOPE;

The Scope statement will cause the value to be calculated whenever the Document Status of [CP] is present, and not otherwise. Very powerful statement, and should get you want you need.