SCOPE and calculated members

MDX 1 Minute

This post on the MSDN forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2064643&SiteID=1

…reminded me of something interesting I found out a few months ago. It seems that whereas you can’t mix regular and calculated measures in a set used in the SCOPE statement, you can rewrite the assignment to avoid using SCOPE and do a direct assignment instead. So, for example, if you were trying to assign to a regular measure and a calculated measure using a SCOPE statement like this:

SCOPE({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]});
    this=1;
END SCOPE;

You would get the following error:

A set has been encountered that cannot contain calculated members.
MdxScript(Cube1) (line, col) A set has been encountered that cannot contain calculated members.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(Cube1) (line, col) The END SCOPE statement does not match the opening SCOPE statement.

You could rewrite the assignment as follows using two SCOPEs:

SCOPE([Measures].[RegularMeasure]);
    this=1;
END SCOPE;

SCOPE([Measures].[CalculatedMeasure]);
    this=1;
END SCOPE;

…but this is clearly a pain, as you’re duplicating your assignment logic. What you can do instead is this:

({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]})=1;

Not as easy to read as using SCOPE, I know, especially if you’re doing something complex, but at least it works! Now I wonder why SCOPE has this problem? Probably something worth opening an issue on Connect about…

Published by Chris Webb

My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.

Published

19 thoughts on “SCOPE and calculated members”

  1. Chris,
     
    I couldn\’t agree more about this.  As you mentioned, it is especially annoying if doing something complex.  For example, lots of cell assignments within the single scope statement.  When adding a new calculated measure that requires these assignments, it\’s nice to specify it in one place (the scope statement) and have it inherit the logic.  I have even found myself doing the cardinal sin of "copy and paste reuse" by creating another scope statement for the new measure in order to reuse (ahem, copy and paste) the assignments.  I\’ve seen this contribute considerably to MDX-script unmanageability (I suppose it keeps us consultants in a job when no one else can figure out what on earth our MDX is doing).

     
    If there is a sound technical reason for disallowing both base and calculated measures in a single scope statement, it would be nice to at least allow reuse of logic.  Maybe something in between code snippets and using statements would suffice.  If we could at least put a placeholder at the start of the cell assignments and another at the end, we could then refer back to it from subsequent scope statements.  I’m sure this would be very easy for the AS team to implement as it would effectively just copy and paste the assignments as required when executing the MDX script.

     
    Christian
     

    Reply

  3. Greetings Chris,I\’m just curious if you\’ve found any more on this topic – mostly why it\’s an issue at all.Thanks,Todd

    Reply

  4. No, I haven\’t actually – I\’ve asked but never had a clear answer. Strange. I also came across a scenario where using this technique to scope across multiple measures lead to much worse performance than separate SCOPEs, but never found out why.

    Reply

  6. Actually , it is a calculated measure, but for hide it from client , i created a new named calculation in DSV then i create scope with this code
    SCOPE({[Measures].[CPC GBP]});
    this=[Measures].[Total Spent GBP]/[Measures].[Clicks];
    end scope;

    finally i add it in denied memeber set that in role >{[Measures].[Spent], [Measures].[Spent GBP],[Measures].[CPC GBP]}

    it is workin for first 2 measures because they are normal measure, but last one is calculate member.

    FYI > If i login with current user , scope working and i get [CPC GBP] value true, but when i select a role then browse cube, it say
    i.e:
    MdxScript(Cube1) (line, col) A set has been encountered that cannot contain calculated members.
    The END SCOPE statement does not match the opening SCOPE statement.
    MdxScript(Cube1) (line, col) The END SCOPE statement does not match the opening SCOPE statement.
    [Measures].[CPC GBP] not fount

    Reply

  8. I don’ t create measure , Firstly i create a named calculation name is > [CPC GBP] then i use this for vlue of this measure, SCOPE({[Measures].[CPC GBP]});
    this=[Measures].[Total Spent GBP]/[Measures].[Clicks];
    end scope;

    This code working good if don’t select any role.

    But when i loggin with a role, it giva error. In the each role i Denied this measure ([CPC GBP])

    Your example looking some different.

    Reply

    1. Yes, but I still think the error is coming from the MDX Script because it’s trying to scope on a measure that it can’t see. Have you tried using a named set inside your Scope statement in the way I suggested?

      Reply

      1. I think error is like you think
        I have tried using a named set inside your issue but maybe use wrong because i didn’t understand well.
        I am try all way that i see on the net.

      2. Hi ,

        There are any control to calculate measure or not(or visible=false) with MDX when client login?

      4. It will take long time , Maybe we can solve problem with named set , maybe i use wrong.

    1. It’s a property on the cube itself – click on the cube in the BIDS Cube Editor and you’ll see it. However I strongly recommend you don’t use it because it means that you won’t see any errors at all, even ones you don’t want to ignore.

      Reply

Leave a Reply to Chris WebbCancel reply