Calculated Member based on dimension attribute value

Question

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 1

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.