SSAS 2008R2 - Scope and Assignments - "A set has been encountered that cannot contain calculated members."

In my previous post I discussed using the SCOPE statement in a cube.   I use it quite extensively in my designs and last week I came accross an unexpected issue with it.

I have a cube containing many measures for which I require different functionality under certain conditions.  Those conditions can actually be described in subcubes, and therefore the SCOPE statement addresses this requirement.

The functionality I want is the same for several measures and therefore I put them together in the same SCOPE block:

 SCOPE([Measures].[Area1],[Measures].[Area2]); THIS=(0); END SCOPE;

Where Area1 and Area2 are both measures defined in the cube.  The above scope would set them both to zero, but this is just for illustration.

The issue arises if the 2 measures above, Area1 and Area2, are actually calculated measures. If that is the case then when we deploy this MDX we get the following error:

 "A set has been encountered that cannot contain calculated members."

This can be confusing as the error message does not actually identify the SCOPE as the source of the issue.  But you can be fairly sure that that is where the problem is.

For some reason using more than one calculated measure in a single scope statment is a problem for SSAS.  If you have very few relevant measures then you could split them into individual scope statments, but this would be laborious if you have several. The solution is to use an assignment script instead. This is as simple as:

({[Measures].[Area1],[Measures].[Area2]}) = 0;

Type this in the calculation script and, hey presto, the error disappears and the MDX works as expected.

Chris Webb also covers this here.