Calculated measure depending on which dimension is currently shown

Question

I am trying to make a calculated measure in my cube that will be different depending on which date dimension is shown. I thought SCOPE was going to get me what I wanted but it seems to do a last pass calculation.

CREATE MEMBER CURRENTCUBE.[Measures].[Sales Prior]
 AS (
    Null
    ),
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Sales';      

SCOPE ([Sales and Forecast Date].[Calendar Year].Members, [Measures].[Sales Prior]);
    this =  ([Sales and Forecast Date].[Calendar Year].CurrentMember.PREVMEMBER, [Measures].[Sales Amount]);
end scope;
SCOPE ([Sales Requested Ship Date].[Calendar Year].Members, [Measures].[Sales Prior]);
    this =  ([Sales Requested Ship Date].[Calendar Year].CurrentMember.PREVMEMBER, [Measures].[Sales Amount]);
end scope;
SCOPE ([Sales Ship Date].[Calendar Year].Members, [Measures].[Sales Prior]);
    this =  ([Sales Ship Date].[Calendar Year].CurrentMember.PREVMEMBER, [Measures].[Sales Amount]);
end scope;

This code snippet only works on the Ship Date dimension and givesNULL for the other 2.

I also tried nesting the SCOPE statements but it did the same thing.

Can you check if [Calendar Year].members is returning [All]. If it is remove with except or replace with [All].CHILDREN. — mxix
I will give it a try. I ended up just declaring each level of the hierarchy separately for each dimension. — TTeeple

mxix mxix · Accepted Answer · 2016-08-22T13:52:53

My best guess is that your "[Calendar Year].Members" is returning [All] which means when you don't actually want to use that hierarchy, it still in SCOPE since [All] is actually the ROOT. So, like you mentioned only the last Hierarchy being mentioned in the SCOPES is working, if you switch the Order of the SCOPES another one should start working, since all the SCOPES are being evaluated but only the last one is actually shown. Try removing the [All] from SCOPE.

SCOPE ([Sales and Forecast Date].[Calendar Year].[All].CHILDREN, [Measures].[Sales Prior]);
    this =  ([Sales and Forecast Date].[Calendar Year].CurrentMember.PREVMEMBER, [Measures].[Sales Amount]);
end scope;
SCOPE ([Sales Requested Ship Date].[Calendar Year].[All].CHILDREN, [Measures].[Sales Prior]);
    this =  ([Sales Requested Ship Date].[Calendar Year].CurrentMember.PREVMEMBER, [Measures].[Sales Amount]);
end scope;
SCOPE ([Sales Ship Date].[Calendar Year].[All].CHILDREN, [Measures].[Sales Prior]);
    this =  ([Sales Ship Date].[Calendar Year].CurrentMember.PREVMEMBER, [Measures].[Sales Amount]);
end scope;

You are doing the scope only for that Level, makes sense only working on that level ,)

If you have an hierarchy with the levels you need you could try something like:

Working it out with DESCENDANTS(,,AFTER) in your scope so all the members in the hierarchy are in SCOPE. I would try it like this:

SCOPE ([Measures].[Sales Prior]);
    this = null;
    SCOPE (DESCENDANTS([Sales and Forecast Date].[Calendar Year].[All].CHILDREN,,AFTER));
        this =  ([Sales and Forecast Date].[Calendar Year].CurrentMember.PREVMEMBER, [Measures].[Sales Amount]);
    END SCOPE;
    SCOPE (DESCENDANTS([Sales Requested Ship Date].[Calendar Year].[All].CHILDREN,,AFTER));
        this =  ([Sales Requested Ship Date].[Calendar Year].CurrentMember.PREVMEMBER, [Measures].[Sales Amount]);
    END SCOPE;
    SCOPE (DESCENDANTS([Sales Ship Date].[Calendar Year].[All].CHILDREN,,AFTER));
        this =  ([Sales Ship Date].[Calendar Year].CurrentMember.PREVMEMBER, [Measures].[Sales Amount]);
    END SCOPE;
END SCOPE;

Calculated measure depending on which dimension is currently shown

2 Answers