Search code examples
ssasmdx

MDX Scope for all attributes of a dimension

I have a dimension X with 10 attributes 1..10. In order to make a custom calculation for measure Y within a scope I can write this statement:

SCOPE([X].[1].CHILDREN, [Measures].[Y]);
 THIS = 1+1;
END SCOPE;

This will make sure that when dimension attribute 1 is selected together with Y then the result will be 2.

Is there any easy way of writing this to make sure it works for all attributes within dimension X instead of using a nested scope and explicitly defining it for all attributes?

Something like SCOPE([X].[*]) or similar?

Solution

  • Create one more calculated member like this:

    CREATE MEMBER DimSelected AS
IIF(
[X].[1].CURRENTMEMBER IS [X].[1].[All]
AND 
[X].[2].CURRENTMEMBER IS [X].[2].[All]
AND 
[X].[3].CURRENTMEMBER IS [X].[3].[All]
AND 
[X].[4].CURRENTMEMBER IS [X].[4].[All]
AND 
[X].[5].CURRENTMEMBER IS [X].[5].[All]
AND 
[X].[6].CURRENTMEMBER IS [X].[6].[All]
AND 
[X].[7].CURRENTMEMBER IS [X].[7].[All]
AND 
[X].[8].CURRENTMEMBER IS [X].[8].[All]
AND 
[X].[9].CURRENTMEMBER IS [X].[9].[All]
AND 
[X].[10].CURRENTMEMBER IS [X].[10].[All],
NULL,
1)

    And modify your scope logic to below:

    SCOPE([Measures].[Y]);
 IF Measures.DimSelected = 1 THEN This = 1+1 END IF;
END SCOPE;

    Alternatively if you just plan to ever select the dim on some given axis, below might work:

    CREATE MEMBER MembersSelected AS
GENERATE(AXIS(1) AS a, a.current.item(0).unique_name)

CREATE member HasX as
IIF(instr(1,MembersSelected,"[X].") <> 0, "Yes", NULL)

    Then modify your scope logic to below:

    SCOPE([Measures].[Y]);
 IF Measures.HasX = "Yes" THEN This = 1+1 END IF;
END SCOPE;