Scoped Assignments and Multiselect

Something interesting to note regarding how scoped assignments behave with multiselect…

On the Adventure Works cube, add the following code to the MDX Script:

CREATE MEMBER CURRENTCUBE.MEASURES.TESTCALC AS 1;
 
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[Calendar Year].MEMBERS);
    THIS = 2;
END SCOPE;
 
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[All Periods]);
    THIS = 3;
END SCOPE;

Then run the following query:

SELECT [Measures].[TESTCALC] ON 0

FROM [Adventure Works]

WHERE([Date].[Calendar Year].&[2001])

 

It returns the value 2 as you would expect. Now run the following query where there is a set in the Where clause, giving a multiselect on 2001 and 2002:

SELECT [Measures].[TESTCALC] ON 0

FROM [Adventure Works]

WHERE({[Date].[Calendar Year].&[2001],[Date].[Calendar Year].&[2002]})

It returns the value 1 – which, strangely, is the value of the original TESTCALC calculated measure before any of the scoped assignments were applied, even though it would seem that the two scoped assignments between them should cover the cells returned by this query.

Now delete the code you added to the MDX Script and add the following:

CREATE MEMBER CURRENTCUBE.MEASURES.TESTCALC AS 1;

 
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].MEMBERS);

    THIS = 4;

END SCOPE;

When you try to run the two queries above you get the value 4 returned in both cases. When I first saw this I thought the results returned by two scenarios were inconsistent and that there was something buggy going on in the first one, but Jeffrey told me there is a specific rule in the engine that dictates this behaviour, so it’s how it’s intended to work – as a result, it’s something that needs to be understood and allowed for in any scoped assignments you write.