Different calculation based on dimension attribute?

Hello

I would like to do this in pseudo code for a calculated member:

if(dim.value == 1) then measure.val

else -measure.val

Anyone have a suggestion?

For AS2005, you should include the following in the MDX script for your cube (your will have to adjust for naming):

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

[Measures].[MyMeasure] * -1,

NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure];

SCOPE ([MyDimension].[MyAttribute].[1], [Measures].[MyCalculatedMeasure];

This = [Measures].[MyMeasure];

END SCOPE;

... and if it's a calculated member defined at run-time.

WITH MEMBER [Measures].[MyCalculatedMeasure] AS

IIF([MyDimension].[MyAttribute].currentmember IS [MyDimension].[MyAttribute].[1], [Measures].[MyMeasure], -1 * [Measures].[MyMeasure]), NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure]

SELECT .....

|||

Thanks for the quick response, I did solve my first problem.. sorta, with SQL in the integration layer. Now I have almost the same problem though. Now I am trying to do this instead:

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

I modified the query like so, but it only returns 0, never the Fact Result AMOUNT measure.

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

0,

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = [Measures].[Fact Result AMOUNT];

END SCOPE;

|||

Please remove the NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT] part.

If you don't remove it - you are risking to get wrong results.

|||

It looks like your logic is reversed. If you want to achieve the following

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

Then the script should be.

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

[Measures].[Fact Result AMOUNT],

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = 0;

END SCOPE;

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

|||

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

No, it is still wrong in this example, because when Fact Result AMOUNT can be NULL, the Cost won't be NULL (it will be 0). Note that in your first example, it was correct, since MyCalculatedMeasure and MyMeasure are always either together NULLs or together not NULLs (assuming that there are no other calculations in the cube).