Reputation: 37
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!
Upvotes: 2
Views: 12821
Reputation: 86
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.
Upvotes: 1