Aggregating the Result of an MDX Calculation Using Scoped Assignments

I don’t usually like to blog about topics that I think other people have blogged about already, but despite the fact that Mosha blogged about this several years ago (in fact more than eight years ago, blimey) this particular problem comes up so often with my customers and on the MSDN Forum that I thought I should write something about it myself. So apologies if you know this already…

Here’s the problem description. If you define a calculated measure in MDX, that calculation will take place after the real measure values have all aggregated. For example, consider a super-simple cube with a Year dimension, two real measures called A and B and a calculated measure called [A * B] that returned the value of A multiplied by B. In a PivotTable you’d see the following result:

image

Note how the Grand Total for the [A * B] calculated measure is 12*16=192, and not 12+12+12+12=48. This is expected behaviour for calculated measures, and indeed the way you want your calculations to behave most of the time.

However, there are some scenarios where you want to do the calculation first and then aggregate up the result of that calculation; in our previous example that means you’d get 48 for the Grand Total instead. Currency conversion and weighted averages are common examples of calculations where this needs to happen. How can you handle this in MDX?

Let’s look at a slightly more complex example than the one above. In the following cube, based on Adventure Works data, I created a Date dimension that looks like this:

image

image

…and a Product dimension that looks like this:

image

image

I also created two measures on a fact table called A and B:

image

Now, let’s say that once again you want to calculate the value of A*B at the Date and Product granularity, and aggregate the result up. Probably the easiest way of handling this would be to do the calculation in the fact table, or in the DSV, and then bringing the result in as a new real measure. However this may not be possible with some types of calculation, or if the granularity that you want to do the calculation is not the same as the granularity of the fact table.

One way of approaching this in MDX would be to create a calculated measure like this:

CREATE MEMBER CURRENTCUBE.MEASURES.[CALC] AS
SUM(
DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Date])
*
DESCENDANTS([Product].[Category – Product].CURRENTMEMBER, [Product].[Category – Product].[Product])
, [Measures].[A] * [Measures].[B]);

image

The big problem with this approach (apart from the fact that it may break when you do a multi-select in certain client tools – but you could work around that) is that it is usually very, very slow indeed. Depending on the calculation, it may be unusably slow. So you need a different approach.

This is where scoped assignments come in. If you make a scoped assignment to a real measure, as opposed to a calculated measure, then the value of the assignment will aggregate up outside of the original scope. So, in this case, since you want the calculation to take place at the Date and Product granularity, if you scope on a real measure at that granularity the result of the calculation will aggregate up automatically.

The first step here is to create a new real (ie not calculated) measure for the calculation. This can be done in the DSV by creating a named calculation on your fact table which returns the value NULL:

image

You then need to create a new real measure on your measure group from this new named calculation:

image

In this example, I’ve left the AggregateFunction property of the measure to be the default of Sum, but you could use a different setting if you wanted a different type of aggregation. The next step is to process the cube, and once you’ve done that you’ll see a new measure that only returns the value 0:

image

Next, you need to create the scoped assignment on the Calculations tab of the Cube Editor. If you remember in my post last week about scoped assignments, I recommended avoiding writing scopes using user hierarchies; using only attribute hierarchies the scope statement becomes:

SCOPE([Measures].[A Multiplied By B]);
    SCOPE([Date].[Date].[Date].MEMBERS);
        SCOPE([Product].[Product].[Product].MEMBERS);
            THIS = [Measures].[A] * [Measures].[B];
        END SCOPE;
    END SCOPE;
END SCOPE;

image

One very important thing to notice: the sets I’ve used for scoping on the Dates and Products do not include the All Member: for example, [Date].[Date].[Date].MEMBERS. If you use a set that includes the All Member, such as [Date].[Date].MEMBERS, the calculation will not aggregate up correctly.

Here’s the result:

image

This is going to be much more efficient than the pure MDX calculated measure approach, though just how well the calculation performs will depend on the complexity of the calculation and the size of the area that you are scoping on.

125 thoughts on “Aggregating the Result of an MDX Calculation Using Scoped Assignments

  1. Hi Chris,

    Checking your blog is my daily work just like checking emails. I would like to see all your posts whether it is a new topic or a repeated one. Personally, I think your explanation is straight forward, clear and precise. It helps me learn new things as well as remember “already know but nearly forgot” stuff. I’m looking forward to seeing your MDX course in Sydney.

    Regards,
    George

  2. Hi chris

    I have calculated memebrs in my cube like this.

    – I created dummy columns in my named queries like “calc memb profit”, “calc memb profit2”, “calc memb profit3” etc.. with value as 0.

    – Then I calculated values for these columns through scope statement like this

    SCOPE ([MEASURES].[CALC MEMB PROFIT]);

    THIS = ;

    END SCOPE;

    this way i have created many calculated measures… and referred them in other calculated measures for example

    SCOPE ([MEASURES].[CALC MEMB PROFIT2]);

    THIS = iif([MEASURES].[CALC MEMB PROFIT] > x,0,[MEASURES].[CALC MEMB PROFIT1]-[MEASURES].[CALC MEMB PROFIT];

    END SCOPE;

    some thing like this…

    Now these calculations are doing fine, but they take really long time while retrieving results.. (more than 5 mins some times)

    Can you please suggest how can I improve performance for these queries.

    Thanks in advance

    • It’s going to be all down to tuning the expressions in the calculated members now, I’m afraid. One thing though: in your IIF you’re returning 0 and you should really be returning null, which may improve performance. Try something like:

      THIS = iif([MEASURES].[CALC MEMB PROFIT] > x,null,[MEASURES].[CALC MEMB PROFIT1]-[MEASURES].[CALC MEMB PROFIT];

      • Thanks a lot for your response chris. I have defined dummy columns in named query SQL, not used new named calculation wizard. If define them as new named calculation, will there be any improvement in performance or both are one and the same.

      • Hi chris, Thanks a million.. Your null suggestion has improved performance by 80%. I have been struggling with this issue for the past 2 weeks and now its almost fixed. Thanks to you from the bottom of my heart.

  3. Thanks Chris.
    I like the idea of using named calculations in the DSV for the new measure; in the past I have created an empty table in the RDB as a source for empty measures to use in scope assignments, but I think the DSV is a better approach. I also concur with George Qiao’s comments.

  4. So glad you took the time to blog this Chris. I have a member from 2 big fact table measures that, I thought, was going to require pre-aggregating and consolidating in the RDBMS (complete redesign). This is a much better solution than the ones I’ve (unsuccessfully) tried implementing the past few days. Most were similar to your calculated member example and were either SLOW or didn’t cover all scopes. This is simple and it preforms extremely well!

  5. Good evening to Mr.Webb and all people reading my post…

    I have the same problem Mr. Webb explained here, getting wrong values with grand total with calculated member…(I have a calculated member depending on a PARENT-CHILD Dimension..all works fine except GRAND TOTAL…Both in Excel and Browsing cube…)

    I tried to do a test creating a NAMED CALCULATION in DSV and then a real measure basis on it.

    But i have problems with creating NAMED CALCULATION..
    I have SQL SERVER 2008 R2 Standard Version..
    Is it possibile to creare a NAMED CALCULATION in DSV also with this version ???
    I do not think so ,,,,Icon in DSV Designer is not active..!!!!
    So I tried to insert that named calcolation manually inside the query of fact table…as below..

    Select , null AS
    from

    I was able to deploy all successfully but when i run OLAP CUBE , it gets an error and tell that
    the name of calculated name i created is invalid…but the name is very simple is CAMPO1 …(what’s wrong?)
    Is it a problem of SSAS ‘Version ???

    Thanks in advance.

  6. Hi, Chris. Great post.
    Do you know if there is an easy way to control solve order if you have a number of these named calculations?

  7. Hi Chris, I have a question.

    If I create a calculated measure then the totals are wrong. Now I understand why this is but if I scope the calculation then I can only view the correct aggregation at that scoped level, which makes sense. So how can I create custom calculated measures where I can get the correct total but avoid having to restrict users to slicing by specified attributes to get the total to work. It seems to me like Microsoft have really fallen short of the mark here in terms of implementation of totals. They should really offer the ability to do this special scoping for specific cases but also offer the ability to calculate the total based on the data delivered to the cube ( making the total separate from the cube space ). I don’t know if I am missing something crucial but it just seems as though a calculation involving various measures is far too complicated that what it should be.

  8. Hi Chriss, thanks for a useful explanation of SCOPE definitions in this scenario. I continue to be amazed at how hard it is in this day and age to properly control the timing of calculations. Since this is quite a common issue, making an option to “calculate at pre-aggregation time” should really be something Microsoft implemented.:-)

    Anyway.

    First I would like to comment that there is a third option as well that may fit some similar scenarios: Deploying hidden calculated members and then using SOLVE_ORDER. There is a decent article about it on TechNet: http://technet.microsoft.com/en-us/library/ms145539.aspx

    Secondly I would like to ask a question: Can you explain why your proposed solution here requires an “empty measure” in the physical data model? I have a scenario where the physical data model and DSV are outside of my control (at least directly; I can create new user-definded objects in the DSV but not edit existing ones). Is there another way I can use your solution, which does not require making changes to the source tabel or DSV?

    • Hi Jesper,

      Actually there are a lot of good reasons not to pre-calculate calculations like this – it would make the cube very large and processing very slow in a lot of cases.

      This solution needs an empty, physical measure because it does not do any of the aggregation in MDX – it relies on way that physical measures aggregate up automatically. This is much faster than aggregating the result of the calculation in MDX. There is no way of using this approach without making changes to the source table or DSV, but if you can create new objects in the DSV you can create a named calculation that returns null and use that for your measure.

      • Great. So I can use an object from a DSV object other than the actual datasource? For example create a new DSV object called “ChrissRules” and use that in my Scope definition? Does the object need to be related to the datasource object?

      • I’m not quite sure what you’re saying here – you do not want to create a new Data Source VIew, but you can create a new named calculation in your existing DSV and build your measure from that. A DSV is not the same thing as a data source.

  9. Sorry I wasn’t more clear:-) What I mean by “data source” is the source database from which a table in the DSV reads data. So what I am getting at is this: Can I create a new table in my existing DSV, name it “ChrissRules”, and insert my named calculation there? Or does my named calculation have to be inserted into the named query which actually reads my source data? All existing named queries in my setup are machine-generated, and any changes I make in them are routinely overwritten. Does this make more sense?:-)

    • Yes, I understand now, thanks! To add a new measure to an existing measure group, your named calculation needs to be on the named query that your measure group uses. If your DSV is machine generated, that will be a problem.

  10. Hi Chris

    Firs of all thanks for the help you are providing to community.

    While building a cube I need to calculate the cumulative sum with one exception. the measure should show the cumulative sum over all the dates except 19000101 which is used for not applicable. when I use
    AGGREGATE(
    {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
    , [Measures].[AVL Amount])
    it gives me cumulative for all the periods including 19000101 which is wrong as we want to exclude the amounts for 19000101. I have modified it to look like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL End Date Amount] as “NA”;

    SCOPE ( {[Measures].[Cumulative AVL End Date Amount]});
    SCOPE (EXCEPT([Contract End Date].[Calendar Dates].[Date].MEMBERS
    , {{[Contract End Date].[Calendar Dates].[Date].&[19000101]}}) );

    THIS = IIF(ISEMPTY([Measures].[AVL Amount])
    ,NULL
    ,AGGREGATE(
    {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
    * {NULL:[Contract End Date].[Calendar Weeks].CurrentMember}
    , [Measures].[AVL Amount])
    );

    END SCOPE;
    END SCOPE;

    now it calculates cumulative correctly but only on the date level when I select another level it shows NA.

    Can you please advise how It can be done for all the date levels and all the time hierarchies.

    Thanks in advance.

    Regards

    Harris

    • Hi Harris,

      I think there’s an easier solution here. I recommend adding a new attribute to your Contract End Date dimension called IsFirstDate that is true for the date 19000101 and false for all other dates. You can avoid the scoped assignment and write your calculation as follows:

      CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL End Date Amount] as
      IIF(ISEMPTY([Measures].[AVL Amount])
      ,NULL
      ,AGGREGATE(
      {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
      * {NULL:[Contract End Date].[Calendar Weeks].CurrentMember}
      , ([Measures].[AVL Amount],[Contract End Date].[IsFirstDate].&[False])
      );

      This should give you the cumulative sum without the 19000101 date and avoids the need for a scoped assignment.

      • Hi Chris

        Thanks for a quick reply.
        I have created a flag on date dimension and set its value true for 19000101 and false for all the other dates.
        i have modified the calculated measure as:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
        * {NULL:[Create Date].[Calendar Weeks].CurrentMember}
        , ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
        );

        it is showing nulls every where for both Calendar Dates and Calendar Weeks hierarchies.

        Regards

        Harris

      • Could be a number of things. Can you first try:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        * {NULL:[Create Date].[Calendar Weeks].CurrentMember}
        , ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
        );

      • CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        * {NULL:[Create Date].[Calendar Weeks].CurrentMember}
        , ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
        );

        Tried this but the same output i.e. null

      • Can you see what happens without the Calendar Weeks hierarchy in the calculation? ie

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        , ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
        );

      • I have already tried to remove the calender weeks hierarchy bu the result was same.

        I have also tried

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
        , [Measures].[AVL Amount])
        );

        It gives me correct cumulative numbers but shows the value for 19000101 equal to base measure i.e. [Measures].[AVL Amount], but the cumulative is only calculated on Date level when I roll up to Month, quarter or year level it shows nulls

        Regards

        Harris

      • OK, it could be that you have some kind of attribute overwrite problem here that will be very difficult to debug remotely. Another approach could be this:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL End Date Amount] as null;

        SCOPE ( {[Measures].[Cumulative AVL End Date Amount]});
        SCOPE (EXCEPT([Contract End Date].[Contract End Date].[Contract End Date].MEMBERS
        , {{[Contract End Date].[Contract End Date].[Contract End Date].&[19000101]}}) );

        THIS = IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
        , [Measures].[AVL Amount])
        );

        END SCOPE;

        SCOPE([Contract End Date].[Contract End Date].[All]);
        THIS = TAIL(EXISTING [Contract End Date].[Contract End Date].[Contract End Date].MEMBERS, 1);
        END SCOPE;

        END SCOPE;

        I’m assuming here that the attribute hierarchy that the date level of your Calendar Dates user hierarchy is based on is called [Contract End Date].[Contract End Date].

      • HI Chris

        I have tried this solution it gives correct cumulative on the date level but again nulls on all other levels.

        Meanwhile after a hit and trial I have found a solution which is working as expected. Here is the mdx:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        * {NULL:[Contract End Date].[Calendar Weeks].CurrentMember}
        * {[Contract End Date].[First Date Flag].&[False]}
        , [Measures].[AVL Amount])
        );

        Now I have to calculate cumulative on other role playing dimensions too. I can create separate calculated measures but then I have long list of measures. I want to calculate single measure for all role playing dimensions. if i use following mdx it gives wrong calculation:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Create Date].[Calendar Dates].CurrentMember}
        * {NULL:[Approval Date].[Calendar Dates].CurrentMember}
        * {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        * {[Create Date].[First Date Flag].&[False]}
        * {[Approval Date].[First Date Flag].&[False]}
        * {[Contract End Date].[First Date Flag].&[False]}
        , [Measures].[AVL Amount])
        );

        I have seven role playing dimensions I just showed three above for understanding purpose.
        I have checked separate measures and they are working fine but not one measure for all.

        Do you see any possibility for this or its better to use separate measures for each role playing dimension.

        Regards

        Harris

      • I recommend you create separate measures for each role playing dimension – that will keep things simple, and you would get some potentially strange results if you tried to create one measure for all your dimensions.

  11. tHANKS Chris,
    I’m having he following problem. I have two fileds C8 and C9 and i have a calculation AvgAbandonTime=C8/C9. There is a location dimension and it gives me the values based off it. Please see the data set below. But in the grand total insted of the average its giving me sum(c8)/sum(c9). Then i followed your process. Created a real measure in the DSV and followed the above procedure, but still getting the same result.
    Location AvgAbandonTime C8 C9 C8DIVC9
    CITY 92 11588 126 92
    COUNTY 16 157 10 16
    DIV 73 62160 857 73
    GrandTotal 74 73905 993 74

  12. Hi Chris,

    How it will work with Parent-Child Hierarchy?

    I have Country dimension (Parent – Child Hierarchy)

    Below sample code I m using :

    SCOPE ([Measures].[Converted Lost Value]);

    SCOPE([Countries].[Countries].Members);

    THIS = SUM(DESCENDANTS([Countries].[Countries].CurrentMember,[Countries].[Countries].[Level 05]),
    ([Measures].[Out of Conformity Value] * ([FE Date].[Fiscal].CurrentMember, [Measures].[Exchange Rate])));

    END SCOPE;
    END SCOPE;

  13. Hi Chris,
    I need to add Customer dimension to have the same behaviour as item. How do I add this into the scope.
    Thanks

  14. Please disregard my previous question. I guess I need to just add

    SCOPE([Measures].[A Multiplied By B]);
    SCOPE([Date].[Date].[Date].MEMBERS);
    SCOPE([Client].[Client].[Client].MEMBERS);
    THIS = [Measures].[A] * [Measures].[B];
    END SCOPE;
    END SCOPE;
    END SCOPE;

    • I think what you need is this:

      SCOPE([Measures].[A Multiplied By B]);
      SCOPE([Date].[Date].[Date].MEMBERS);
      SCOPE([Client].[Client].[Client].MEMBERS);
      SCOPE([Product].[Product].[Product].MEMBERS);
      THIS = [Measures].[A] * [Measures].[B];
      END SCOPE;
      END SCOPE;
      END SCOPE;
      END SCOPE;

      • Hi Chris and thank you for your quick answer. I really appreciate it since this is kind of an urgent issue.
        Your recommendation will work fine if user chooses date, client and product at the same time.
        What I want is to give them ability to use either date – client or date – item option and aggregate total on both levels. In this case it does not provide correct result.

      • What my code should do is aggregate from the date, client *and* product granularity in all cases, regardless of what is chosen. I think what you want is to aggregate from client only if client is chosen, but this means you run into a common problem with advanced MDX calculations: something is always chosen from a hierarchy all the time! For this reason I always recommend to my customers that trying to write calculations that do something different depending on what has been chosen should be avoided: it is possible to do this up to a point, but the code is complex, usually slow, and the results turn out to be confusing for end users. It’s better to have different calculations for different scenarios.

      • Hm, I need exactly the same behaviour regardless of what user selected (date, client and / or item). I did some testing and it did not look right. Will probably need to do more testing.

      • I think I got you. I should have 2 calculations then. One for Date / Client scope and another for Date / Item scope right?

  15. Hi Chris,
    It does not work when I try to add more dimensions to have the same calculation:

    SCOPE([Measures].[A Multiplied By B]);
    SCOPE([Date].[Date].[Date].MEMBERS);
    SCOPE([Product].[Product].[Product].MEMBERS);
    THIS = [Measures].[A] * [Measures].[B];
    END SCOPE;
    END SCOPE;
    END SCOPE;

    SCOPE([Measures].[A Multiplied By B]);
    SCOPE([Date].[Date].[Date].MEMBERS);
    SCOPE([Client].[Client].[Client].MEMBERS);
    THIS = [Measures].[A] * [Measures].[B];
    END SCOPE;
    END SCOPE;
    END SCOPE;

    Is the second scope overwrites the first one.
    Thanks

  16. If measure A and measure B are in two different fact tables, such that

    Measure A is associated with Dimension X
    Measure B is associated with Dimension Y

    then the scope mdx doesn’t seem to work.

  17. Hi Chris,

    Regarding the grand total issue,

    I have close to 15 dimensions in my cube and I have 7-10 calculated measures that are of the form
    [Measures].[A] * [Measures].[B] or [Measures].[A] / [Measures].[B]

    These calculated measures can be seen from all dimensions. Should the scope statement you have mentioned include all the attributes of all the dimensions as users will be using them from the pivot fields?

    If so, the calculation code will be so huge. Also the option where you have suggested to write specific scopes for specific combination also will turn out to be huge as combinations for 15 dimensions are so many

    Can you please suggest an approach?

    Thanks

    Uday

    • You could use the Leaves() function here, but in this case why don’t you just calculate the values in your fact table in SQL Server (or whatever RDBMS you’re using)? It would give you the same result.

      • Hi Chris,

        Thanks for your reply.

        But does every calculated measure need to be created in sql server or dsv. In my case the calculated measure depends on two different fact tables. Can the cube not handle this via MDX?

        Thanks

        Uday

      • No, certainly not – it’s only in the special cases that this post describes that you need to create calculations in this way. Most calculated measures in MDX are calculated after the real measures have been aggregated – only a few types of calculation need to be aggregated after the calculation has taken place.

  18. I had the same issue where end users were publishing “sales price” and “quantity” to the cube. At the input level the “sales amount” = “Sales Price” * “Quantity”. This needs to be handle as a two pass calc. So all I did was scope at the child level of the Dimension ID that linked from the hierarchy table to the measure table. Did this calc then did the calculate. Worked great. Here’s what it looks like. If you have more dimension tables that are at a lower level of granularity I would assume you just add their attributes keys to this scope statement

    SCOPE ([Detail].[Forecast Id].children);
    [Measures].[Sales]
    = IIF([Measures].[Qty]=0,NULL,[Measures].[Selling Price] * [Measures].[Qty]);
    END SCOPE;

    CALCULATE;

  19. Chris, Thanks for this magnificent post as usual, I’ve really benefited from it.
    However I couldn’t understand why to use a real measure not a calculated measure. That part of the post was not clear to me. would you please explain it more? especially what do you mean by “the value of the assignment will aggregate up outside of the original scope” ? what is the original scope? can you please make it simpler?

    Thanks again:)

    • It has to be a real measure because only real measures aggregate up – calculated measures don’t. Think of a very simple hierarchy with an All Member and two members, A and B, beneath it. For a real measure, the All Member is the aggregated value of A and B; this is not the case for a calculated measure.

      To answer the second part of your question, using the same hierarchy as in the previous paragraph, if you create a scoped assignment on member A then for a real measure the All Member’s value will change so that it is still the aggregated value of A and B.

      • Thanks Chris, just to make sure that I’ve understood you correctly. Do you mean that if the wanted measure = [measure X]*0.5, and using your example hierarchy:

        – In real measure case, the All Member = (([A],[measure X])*0.5) + (([B],[measure X])*0.5)
        – While in calculated measure case, the All Member = ([ALL],[measure X])*0.5

        Is this what do you mean? and, does any case (real or calculated measure) has better performance?

  20. nice post Chris! I’ve recently created a new SSAS cube with SSDT 2012 against SQL2012 and got the red squiggly line under the first word of “scope” (like your screenshot in your post). Due to this problem, the cube can’t be deployed. If I copy the same scope statement to SSDT 2010, it seems no problems. I don’t know have you run into this issue before, I can’t image I am the only one has this problems, not sure if I should use SSDT 2010 instead of SSDT 2012. Thanks.

  21. Hi Chris,

    Pretty new to MDX here and have found you’re blog to be great reading and very helpful.

    I have a weighted average calculation that does ((A * B ) / A) but only where A is greater than 0. I’ve been trying to determine if I can leverage what you did with the SCOPE statement but I’m having trouble with excluding the data I don’t want included in the calculation.

    Can I do that with what you demonstrated above or am I barking up the wrong tree?

    Thanks.

  22. I forgot to mention that B is actually the result of two other measures being divided to get a percent (B = C / D).

    Thanks.

    • Yes, using a SCOPE statement would be the way to go. Define B as a regular calculated measure, create your dummy ‘real’ measure for the weighted average, then overwrite it with a SCOPE statement something like this:
      SCOPE(MyDim.MyHierarchy.MyLevel.Members, Measures.WeightedAverage);
      THIS = iif(Measures.A=0, null, ((Measures.A*Measures.B)/Measures.A));
      END SCOPE;

    • Hi Chris,

      After being sidelined by another project, I’m back trying to get my weighted calculation to work. I think I’m close but my weighted average ends up as a sum instead of a percent. I’m probably missing something obvious.

      The calculations I need are based on 3 measures:
      * Inspections (I)
      * Passing Inspections (P)
      * Volume (V)
      Volume is in a different fact table than the inspection data.

      Average Quality (AQ) is defined as P / I. I created a calculation in the cube for this.
      Weighted Average Quality (WA) is defined as ((V * AQ) / V) where there is an AQ value. I’ve created this as a measure in the source.

      Some sample data.

      Date I P V AQ
      02/01/2015 4 3 100 75.00%
      02/02/2015 1 1 7 100.00%
      02/03/2015 0 0 11 N/A

      Based on the sample data, my Weighted Average should be ((100 *.75) + (7 * 1)) / (100 + 7) = ( 75 + 7 ) / 107 = 82 / 107 = 76.64%

      The SCOPE statement I’m using is

      SCOPE([Measures].[WA]);
      SCOPE([Date].[YQM].[Date ID].members);
      THIS = iif
      ( [Measures].[AQ] = 0
      , NULL
      , ( [Measures].[V] * [Measures].[AQ] ) / [Measures].[V]
      );
      END SCOPE;
      END SCOPE;

      There is not ALL member at this hierarchy level [Date].[YQM].[Date ID]. The Weighted Average I get for the sample above is 175.00%.

      Can you help point me in the direction of what I’m doing wrong. Should I be using the SCOPE on the component pieces to exclude the rows I don’t want as part of the Weighted Average?

      Also, can you recommend a better way to test SCOPE statements outside of reprocessing the cube? It doesn’t seen to work with SSMS.

      Thanks.

      • Hi Tom,

        I think the mistake you’re making is that your scope statement is summing up the result of the whole calculation, whereas you actually want to sum up just the result of V * AQ and then divide the result by V. Something like this should work:

        SCOPE([Measures].[WA]);
        SCOPE([Date].[YQM].[Date ID].members);
        THIS = iif
        ( [Measures].[AQ] = 0
        , NULL
        , ( [Measures].[V] * [Measures].[AQ] )
        );
        END SCOPE;
        END SCOPE;

        CREATE MEMBER CURRENTCUBE.MEASURES.WA1 AS
        IIF(MEASURES.V = 0, NULL, MEASURES.WA/MEASURES.V);

        The calculated measure WA1 should give you the result you need.

  23. Chris I have a calculated measure that returns an average price by dividing an amount measure by a quantity measure. The value of the calculated measure is correct at the day level but not for the total for the month because its actually showing the average of the daily calculated measure members rather than the result of the average price calculation using the totals of the amount and quantity measures for the whole period?

    • How are you doing your calculation? It should be fairly straightforward, something like:
      CREATE CURRENTCUBE.MEASURES.MYCALC AS IIF(MEASURES.[QUANTITY]=0, NULL, MEASURES.[AMOUNT]/MEASURES.[QUANTITY]);

      • Chris thank you for your response. It turned out that I had an error in my calculation and once I had corrected it the normal aggregation behavior returned the correct results. During the process of resolving my issue I watched one of your webcasts where you used a “Deploy script” button to push calculations to the cube without having to do a full deploy and process of the project. After doing a little more research I found that this functionality is part of “BIDS Helper” and available for download from Codeplex. This discovery was invaluable as it made it so much easier to debug the calculated measure script. So thanks again for your help and please keep up the excellent work!

  24. Dear Chris

    I don’t have many experience in MDX and starting in this new world I have learned too much from your blog in order to solve many problems that I have found during the process and want to give the thanks for excelent work that you have done.

    In this case, I’d like to receive your help in this calculation that is driving me crazy:

    I have a
    – Geography dimension (Country -> State -> City)
    – 2 related Fact Tables: “Revenues” (with measure Revenue) and “Revenue Goal” (with measure Revenue Goal) and created
    – A dummy “Real” measure into the Revenue Fact table (in the dsv) to calculate the Revenue not planed (by city) using Scope, this is called “NotPlannedRevenue” with null like default value.

    Later using Scope I have calculated the values in order to get correctly Totals Values over the a Geography hierarchy which is working properly.

    The calculate consists in aggregate the revenues from a city if the revenue goal is cero. The MDX script for the measure is

    SCOPE([Measures].[NotPlannedRevenue]);
    SCOPE ([Geography].[GeoHier].[City].Members);
    THIS=iif([Measures].[Revenue Goal]=0 , [Measures].[Revenue] ,0 );
    END SCOPE;

    SCOPE( [Geography].City.[All]);
    This =sum( Descendants([Geography].[GeoHier].CurrentMember,,LEAVES),[Measures].[NotPlannedRevenue]);
    END SCOPE;

    SCOPE( [Geography].State.[All]);
    This =sum( Descendants([Geography].[GeoHier].CurrentMember,,LEAVES),[Measures].[NotPlannedRevenue]);
    END SCOPE;
    END SCOPE;

    The “Totals” are well calculated, but the issue is when I filter Cities from the Geography dimension, the total values are not being calculated using only the selected cities, it use all the cities from the fact table. I have problems in the totals by City, State, Country.

    On this URL you cand find screenshots from te data:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cea11a09-6173-43bd-a263-9c1a264eb0ff/totals-calculation-issue-when-i-filter-member-used-in-an-scope-calculation-with-descendant?forum=sqlanalysisservices

    I appreciate your help to solve the way which I must write this MDX or maybe I have to use another strategy., Thanks!

    • Chris as you suggested on Microsoft forum, I removed 2 SCOPE statements (on the All members of City and Country) and the calculation works properly.

      Thanks too much!

  25. Hi Chris,
    I would appreciate, if you could explain one thing about SCOPEd assignments. It is related to currency conversion. Since I have many-to-one conversion, I’m trying to do it in ETL: in my fact table for transactions I have 2 columns – one for original amount and one for amount in EUR. Then, I have 2 corresponding measures in my cube: [Amount] and [Amount EUR]. I have Reporting Currency dimension with 2 members EUR and Local. Then, in my calculations, I have, for example:
    Scope (Measures.[Amount]);
    Scope([Reporting Currency].[EUR]);
    Scope(Measures.[Amount]);
    This = Measures.[Amount EUR];
    Non_Empty_Behavior(This) = Measures.[Due Amount];
    End Scope;
    End Scope;
    End Scope;

    I.e., I simply assign [Amount EUR] to [Amount]. Is this a good way? I mean, would it affect performance anyhow? For example, if one of the users selected EUR as reporting currency, and another one selected Local currency at the same time in some report? What happens in the cube in this case?
    I have never seen any explanation for such cases, so I do not know if it is right or wrong way.

    • There is always going to be some overhead using scoped assignments or indeed any MDX calculation, but this is very simple so you probably won’t notice any overhead. You will need to make sure that the IsAggregatable property is set to false on the Reporting Currency hierarchy, though. Why do you scope on Measures.Amount twice? You can probably delete the second scope statement.

      Also, remember that Non_Empty_Behavior is deprecated and so you probably shouldn’t use it:
      https://cwebbbi.wordpress.com/2015/01/27/deprecateddiscontinued-functionality-in-ssas-2014/

      • Wow, that was fast! Thank for explanation.
        IsAggregatable set to False for Reporting Currency, indeed. I have SSAS 2012, so Non_Empty_Behavior shall still be there, right? There is a typo, there should be Non_Empty_Behavior(This) = Measures.[Amount];
        I have a double scope on Measures.Amount because it is similar to what BI wizard offers when doing currency conversion in the cube. I could never understand, why it wants to scope twice on measures, but thanks anyway for pointing out.

        Still, currency conversion in the cube shall perform worse that one in ETL, right? I mean, this is what BI wizard offers me for many-to-one conversion:
        Scope ({Measures.[Amount] });
        Scope( Leaves([Dates]) ,
        [Reporting Currency].[EUR],
        Leaves([Currencies]));
        Scope({Measures.[Amount]
        });
        This = [Reporting Currency].[Local] / Measures.[Exchange Rate];
        End Scope;
        End Scope;
        Format_String(This) = “# ### ##0.00”;
        End Scope;

        Is ETL way better than doing currency conversion in the cube?

      • Yes, if you are doing many-to-one currency conversion, doing the conversion in ETL is the right thing to do – it will give you faster query performance than if you did it in MDX.

  26. Hello ,
    My problem is like below
    Tier Sale Avg
    TierA 25 150
    TierB 35 165
    TierC 42 146
    TierD 35 175
    Total 137 636
    Grand total 0.215408805
    I have dimension tier and two measure sale and Avg. my requirement is as below
    I want to display Grand total as below 137/636 =0.21540885 in excel
    Please help

    Regards,
    sanjeevan

    • Which measure do you want this calculation to appear as the Grand Total for? If it’s for Sale, you could do something like this:
      SCOPE(MEASURES.[SALE], [Tier].[Tier].[All Tier]);
      THIS = IIF(MEASURES.[AVG]=0,NULL,MEASURES.[SALE]/MEASURES.[AVG]);
      END SCOPE;

      This scopes on the All Member on the Tier hierarchy and the SALE measure, and replaces the value with the result of the calculation.

  27. Thanks so much for your help. I figured I would need to do the sums via the SCOPE and then apply the formula but you saved me a lot of trial and error.

  28. Hello Chris ,
    I want to write MDX
    my Scenerio is like below

    Lets have

    Month Item Cost
    Jan AA 10
    JAN BB 20
    JAN CC 30
    FEB AA 15
    FEB CC 20
    MAR AA 70
    MAR BB 52

    I need output like belo
    Item Cost
    AA 70
    BB 52
    CC 20
    Means last non empty value of particular Item .

    Regards,
    Sanjeevan

      • Thanks Chriss !!!
        I am able to achive the solution of my query mentioned above by using the code below ,you mentioned in your post

        MEMBER MEASURES.[Last Sale Original] AS
        TAIL(
        NONEMPTY({NULL:[Date].[Date].CURRENTMEMBER} * [Measures].[Internet Sales Amount])
        ).ITEM(0)

        Once again thanks

  29. Hello Chriss,
    My problem is like below

    I have to native measure [Internet Extended Amount],[Internet Freight Cost], I sliced it with Product model

    my Grand Total Should be $566.57 for [Internet Extended Amount] measure
    Grand Total= (Internet Extended Amount * Internet Freight Cost)/SUM ( Internet Extended Amount for selected Model name )

    Values
    Row Labels Internet Extended Amount Internet Freight Cost
    Bike Wash $7,218.60 $180.51 $11.93
    Classic Vest $35,687.00 $892.18 $150.75
    Cycling Cap $19,688.10 $492.31 $55.22
    Fender Set – Mountain $46,619.58 $1,165.49 $348.67
    Grand Total $1,09,213.28 $2,730.49 $566.57
    Grand Total $566.57

    Thanks
    Sanjeevan

  30. […] it's quite convenient. One possible way round this would be to use the technique I describe here: Aggregating the Result of an MDX Calculation Using Scoped Assignments | Chris Webb's BI Blog You could scope your calculation at the date level and it I think it should aggregate up to give […]

  31. Hello. I need to create a calculated member. I have a dimension called GEO with 4 levels (Geo, Sub-Geo, Country and Sub-Country). When the Country is US I need to use a Measure X, but for all the another countries I need to use Measure Y. This should apply even if I am in the ALL level. I tried something like
    IIF (Country.CurrentMember = US, Measure X, Measure Y). It works only for the Country level, but when the aggregations is in ALL level or another Level like Sub-Geo, the calculated member always use Measure Y.

  32. Hi Chris,

    I’m pretty sure that the explained solution would work as well for my case but I don’t retrieve the expected result.

    First of all, the model is as follow: 1 fact table “Sales”, 1 regular dimension “Product”, 1 intermediate fact table “Product_Market” and 1 many to many dimension “Market”.
    The fact table “Sales” contains a measure “Quantity” that we need to mutliply by the measure “Factor” from the intermediate fact table.
    FYI: The factor can be different for the set Product-Market. This is why we do not have the “Factor” measure in the “Sales” fact table.

    Could you tell me whether your proposed solution can work with a many to many?

    Thanks and regards,
    Jérémie

    • Yes, it should work I think; your calculation is basically the same as a currency conversion. If you’re using Enterprise Edition you’ll find it’s much easier to use Measure Expressions for your calculation if it’s just a simple multiplication or division.

      • Dear Chris,
        we have implemented the proposed solution but we are fuguring out a behaviour that we cannot explained sa far. Maybe could you read the below SCOPE and tell me whether you see what the root cause would be. Thanks in advance.

        With this SCOPE, the value of the calculation on each level is 0:
        SCOPE([Measures].[Unit Multipliedby Factor]);
        SCOPE([Sales date].[Sales date – key].[Sales date – key].MEMBERS);
        SCOPE([Product market].[Product market by market – level10].[Product market by market – level10].MEMBERS);
        THIS = [Measures].[units] * [Measures].[market factor];
        END SCOPE;
        END SCOPE;
        END SCOPE;

        With this scope, the value of the calculation is OK on lower level but not on higher level:
        SCOPE([Measures].[Unit Multipliedby Factor]);
        SCOPE([Sales date].[Sales date – key].MEMBERS);
        SCOPE([Product market].[Product market by market – level10].MEMBERS);
        THIS = [Measures].[units] * [Measures].[market factor];
        END SCOPE;
        END SCOPE;
        END SCOPE;

        What I understood from your explanation it that using sets for scoping on the Sales date and Product market must not include the All Member. SO the 1st SCOPE statement should meet this requirement where the second not. What we do not understand is why the first statement returns 0.

        Thanks again in advance for your support.
        Jérémie

      • The first MDX should be correct – each of the hierarchies used is an attribute hierarchy, isn’t it?

        Also, [Measures].[Unit Multipliedby Factor] must *not* be a calculated measure.

      • The attribute hierarchies are well used in the first MDX.

        [Measures].[Unit Multipliedby Factor] is a measure in the Sales Measure Group. Not a measure in the Calculations tab.

        We continue the investigation…data type requirement?

  33. Hi Chris,

    Again, I have a problem that requires a non-trivial approach with scoped assignments. The situation is following: I have a single measure Saldo (Sum) and Countries dimension in my cube. The Countries dimension contains plain country members, and also a Group member, which contains the sum of all countries. Of course regular [All] member contains duplicate amount if i select any country and Group member, because Group already includes this country.
    Now, what I’m trying to achieve is following, in Excel at least:
    1) If Group member is selected (rows, columns, filter), the total for Saldo should be equal to the Group member value.
    2) If Group member is not selected, the total for Saldo should be equal to sum of selected countries.

    Of course, I’m trying to achieve this with scoped assignments. I created an additional empty physical measure Non Group Saldo, and assign value to it as following:

    Scope(Leaves([Countries]));
    Scope([Measures].[Non Group Saldo]);
    If NOT [Countries].[Country Name].CurrentMember IS [Countries].[Country Name].[Group] THEN This = [Measures].[Saldo] END IF;
    End Scope;
    End Scope;

    Then, for #2, I do it like this:
    Scope(Root([Countries]));
    Scope([Measures].[Saldo]);
    FREEZE([Measures].[Non Group Saldo]);
    This = [Measures].[Non Group Saldo];
    End Scope;
    End Scope;

    #2 works fine, but how to make #1 work as I want? Is that possible with scoped assignments?

    Let me illustrate #1 and #2.
    #1:
    USA 100
    Canada 70
    Group 500
    ————-
    Total 500

    #2:
    USA 100
    Canada 70
    ————-
    Total 170

    Of course, i can change dimension design and make it parent-child, but I would like to avoid it, because I’m not the original developer.

    • I found the solution, may be useful to someone.
      First, i added a calculated member Group Saldo:
      CREATE MEMBER CURRENTCUBE.[Measures].[Group Saldo]
      AS Aggregate([Countries].[Country Name].[Group], [Measures].[Saldo]),
      VISIBLE = 0;

      Then, i added one physical empty measure Group Detected as Sum, and a scope assignment for it:
      Scope([Countries].[Country Name].[Group], [Measures].[Group Detected]);
      This = 1;
      End Scope;

      Which basically means that if a Group member is selected, [Measures].[Group Detected] equals 1 on [All] level as well.

      Then, I changed #2 as following:
      Scope(Root([Countries]));
      Scope([Measures].[Saldo]);
      FREEZE([Measures].[Non Group Saldo]);
      This = [Measures].[Non Group Saldo];
      IF [Measures].[Group Detected] = 1 THEN This = [Measures].[Group Saldo] END IF;
      End Scope;
      End Scope;

      Which means that i check if Group member is in selection and set Total for Countries to Group Saldo in this case.

  34. Hi Chris,
    I need one MDX script
    Below is the relational query
    Select COUNT(distinct empID) from Emp
    Where EmpType >10
    I have Employee Dimension with attribute empID ,EmpType
    I created distinct count (Employee.EmpID.EmpID) as Measure
    How can i get the distinct empID count using EmpType in MDX

    • Hi Chris,
      I need one MDX script
      Below is the relational query
      Select COUNT(distinct empID) from Emp
      Where EmpType >10
      I have Employee Dimension with attribute empID ,EmpType
      I created distinct count (Employee.EmpID.EmpID) as Measure
      How can i get the distinct empID count using EmpType in MDX

  35. Hi Chris,
    I need one calculated member in my cube below i the scenario please help me.
    I have one dimension with attribute name as Status Type it has values 9,10,11,12,13,14,15 and i have one calculated measure as distinct count of (ID)
    Now i need to calculate distinct count (ID) based on Status type below are the requirements for calculation
    1)if dimension.status type.statustype value >10 then i need distinct count(ID)
    2)if status type <=10 then i need distinct count(ID)

    Please help me..

  36. SCOPE(MEASURES.[EMP DISTINCT COUNT], NULL : EMPLOYEE.EMPID.&[9]);
    THIS = NULL;
    END SCOPE;
    This expression not working as expected it display the Null for only status type 10 ,rest of the status type values not chaged even though 9 also populated the value as per the requirement 9 shoud be null

  37. Hi Chris

    Great post, thanks.

    I have a question please which I think this post answers but I wanted to check.

    I have 2 fact tables at different grains and need to create a calculated measure using them both.
    CallSummary (EmployeeKey, ApplicationCodeKey, CallDateKey, AvailableMinutes)
    ProductiveTime (EmployeeKey, DateKey, ProductiveHours)

    My calculated measure = AvailableMinutes/ProductiveHours.

    So this calc will only be accurate from the Employee, Date level upwards. Can I just create the calc and scope it ? (Im new to SSAS so will have to play around with getting the scoping correct). Or do i need to create a named query over the CallSummary table and aggregate it up to the EmployeeKey, CallDateKey level and then use this new table for my measure group and calculated measure? (Or create this new summary table in the ETL)

    Many thanks
    Garry

    • Hi Gary,

      You need to scope the calc from Date and Employee yes – bit you don’t want to aggregate the results of the calculation I think (ie don’t do what I’m doing in this blog post, you don’t ever want to sum up the result of Productive Time). So just create a calculated measure that returns null and then overwrite it with a scope statement something like this:

      create member currentcube.measures.productivetime as null;

      scope(measures.productivetime, Employee.EmployeeKey.members, Date.Datekey.members);
      this = iif(measures.productivehours=0, null, measures.availableminutes/measures.productivehours);
      end scope;

      No need to create a named query or any regular measures.

  38. Thanks Chris, that was a great help.

    The calculation is working on EmployeeKey but not on the others? My scope looks like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[AMPH] AS Null, VISIBLE = 1 ;

    SCOPE([Measures].[AMPH], [Employees].[Employee Key].members, [Rosters].[Start Date Key].members, [Call Date].[Date].members);

    this = iif(
    [Measures].[Productive Minutes] = 0,
    null,
    ([Measures].[Available Time] / 60.0) / ([Measures].[Productive Minutes] / 60.0)
    );

    BACK_COLOR(THIS) = RGB(255,128,64); –Orange

    END SCOPE;

    I think its because the example I sent you I had simplified so I could understand the theory. Looking at the above you will see I have scoped to [Rosters].[Start Date Key].members. This is because my fact table that I mention as such: ProductiveTime (EmployeeKey, DateKey, ProductiveHours), actually has a RosterKey and not a DateKey. The RosterKey joins to a Roster dimension table that has the DateKey on it. The DateKey joins to the Date dimension table (snowflake schema). That said, I am including the Date in my Roster dimension as an attribute instead of having another Date cube dimension (I have a lot of dates and so having a separate cube dimension each time becomes messy for the user.)

    So, looking at my Dimension Usage tab, the Employee dimension is referenced by both the Productive Time and Call Summary measure groups but then the Rosters dimension only references the Productive Time and the Call Date dimension only references the Call Summary. Is that the problem ? Does a calculated member, that uses measures from 2 different measure groups, need to be scoped only to a dimension that also references both measure groups?

    I have tried to write 2 separate scope statements and scope at different levels but cant seem to get it to work.

    Thanks again
    Garry

  39. Hi

    Okay, the only way I could get it to work was to define the Dimension Usage like this:

    Issues i have with this are:
    1. My Roster dimension is now related to my Call Summary measure group via my Call Dates dimension. Doesnt seem correct to be doing that.
    2. My Date dimension that is role playing as a Call Date dimension is now linked to my Roster Start Date. I dont want a seperate date dimension for my Roster Start Date and if i did, i would add the Date dimension as another role playing dimension of ‘Roster Start Date’….but it seems i would not be able to do this and have my calculation work.

    There is something I am not understanding here…..feels like I am starting to hack this together instead of doing it properly.

    Many thanks
    Garry

  40. When i included the below measure in my query execution time jumped by 2 hrs. How to rewrite the below measure.

    MEMBER [Measures].[Med] AS MEDIAN( filter ( NONEMPTY (
    [Dimension].[Key].CURRENTMEMBER.CHILDREN
    ,[Measures].[Cycledays])
    ,[Measures].[Count]=1)
    ,[Measures].[Cycledays])

  41. Hell people, i have a problem that needs very urgent help.

    I am trying to write a query

    with member [Measure].[Distinctcount]
    as
    distinctcount({[Order Created Date].[Order Created Day Name Of Week],[Measures].[Ordered Qty]}

    )

    select
    non empty
    {

    [Order Created Date].[Order Created Weekday Weekend]

    , [Measure].[Distinctcount]
    }
    on columns
    ,
    non empty
    {

    [Site].[Site Hierarchy].[Site Name].allmembers
    }
    on rows

    from (

    select
    {
    [Order Created Date].[Order Created Week Hierarchy].[Order Created Date].&[20151101]
    –:[Order Created Date].[Order Created Week Hierarchy].[Order Created Date].&[20151130]
    }
    on columns
    from [The Cube]
    )

    but all i continue to get is

    Executing the query …
    The Distinctcount calculated member cannot be created because its parent is at the lowest level in the Title hierarchy.
    Execution complete

    can anybody help me with this?

    • I think there’s a typo in your code: you have written [Measure].[Distinctcount] and you probably should have written [Measures].[Distinctcount] if you wanted to create a calculated measure.

  42. Hi Chris,

    I am struggling with scoped measures.Measure ‘X’ gets its value from different fact tables.Lets consider Time (Fiscal Week, Month)dimension and Channel dimension. For different combination of attributes in these two dimensions X will get its value from different tables as follows:

    Week + Channel – gets from table FactTrafficByWeekChannel
    Week – gets from table FactTrafficByWeek
    Month + Channel – gets from table FactTrafficByMonthChannel
    Month – gets from table FactTrafficByMonth
    Note: Weeks values cannot be rolled up month as it is visitors count measured for different periods(Week, month, quarter)
    To achieve this I added these fact to cube and created a calculated measure and scope scripts to overwrite the scope. Following is the scope script statement:

    CALCULATE;
    CREATE MEMBER CURRENTCUBE.[Measures].[Y]
    AS (0),
    FORMAT_STRING = “Standard”,
    VISIBLE = 1;
    Scope
    ([Measures].[Y],[Dim Time].[Fiscal Week].[Fiscal Week].Members
    ) ;
    This = [Measures].[X – Vw Fact Total Weekly Traffic];
    End Scope ;
    Scope
    ([Measures].[Y],[Dim Time].[Fiscal Week].[Fiscal Week].Members,
    [Dim Campaign].[Channel].[Channel].Members
    ) ;
    This = [Measures].[X – Vw Fact Total Weekly Traffic By Channel];
    End Scope ;
    Scope
    ([Measures].[Y],[Dim Time].[Fiscal Month].[Fiscal Month].Members
    ) ;
    This = [Measures].[X – Vw Fact Monthly Traffic];
    End Scope ;
    Scope
    ([Measures].[Y],[Dim Time].[Fiscal Month].[Fiscal Month].Members,
    [Dim Channel].[Channel].[Channel].Members
    ) ;
    This = [Measures].[X – Vw Fact Monthly Traffic By Channel];
    End Scope ;

    Above code works fine when corresponding dimension attributes are dragged in browsing pane but do not work when added to filter pane.Problem is I believe there is no way to know the attributes selected in filter pane in multidimensional cube. But I guess DAX function ‘IsFiltered’ identifies whether an attribute in being filtered or not. Is there an equivalent function in MDX ? Or, is there any other way of doing this ?
    Can anybody help me with this?

  43. Hi Chris,

    I’m attempting to replace measure values in the fourth level of a parent-child hierarchy via a case statement, but the subtotal of the third level is displaying the total as if the values were not being replaced even though the subtotal of the fourth level aggregates as expected. Any idea as to what I’m missing? Thanks so much for your help!

    SCOPE
    ([Measures].[Balance Sheet]);

    Scope([Master Date].[Calendar].MEMBERS);

    SCOPE
    [Statements].[Statements].[Level Four].Members;
    Scope [Statements].[Statements].Members;
    this =
    CASE
    WHEN
    (NOT
    [Statements].[Statements].CurrentMember
    IS
    [Statements].[Statements].[Level Four].[Net profit/(loss)])
    AND
    (NOT
    [Statements].[Statements].CurrentMember
    IS
    [Statements].[Statements].[Level Four].[Retained Earnings])
    THEN
    [measures].[ending balance]
    WHEN
    [Statements].[Statements].[Level Four].[Net profit/(loss)]
    IS
    [Statements].[Statements].[Level Four].[Net profit/(loss)]
    AND
    (NOT
    [Statements].[Statements].CurrentMember
    IS
    [Statements].[Statements].[Level Four].[Retained earnings])
    THEN
    [Measures].[Net Change]
    WHEN
    [Statements].[Statements].[Level Four].[Retained earnings]
    IS
    [Statements].[Statements].[Level Four].[Retained earnings]
    AND
    (NOT
    [Statements].[Statements].CurrentMember
    IS
    [Statements].[Statements].[Level Four].[Net profit/(loss)])
    THEN
    [measures].[beginning balance]
    END;
    END SCOPE;
    END SCOPE;
    End scope;
    End Scope;

    • Is Balance Sheet a calculated measure? If so, that would explain why it isn’t aggregating up. Otherwise, this SCOPE statement looks suspicious:

      SCOPE
      [Statements].[Statements].[Level Four].Members;
      Scope [Statements].[Statements].Members;

      Is that what you actually have? There are brackets missing here for a start, so I would be surprised if this worked.

      • I’m also noticing that the case statement order determines the overall subtotal of level 3. For example, if I put the case for the beginning balance measure first, the subtotal equals the value of the beginning balance cells at that level. Could it be something wrong with the case statement?

      • I must have not replied directly to your comment and created a new comment below. Sorry about that.

        Here is my original reply to your first comment:

        Sorry about that. I do have the brackets around the scope statements. MDX Studio stripped them out when I formatted the query. The aggregations work fine going up to the first level. The subtotal is only off on the third level subtotal where i’m replacing the measures.

        Balance sheet is a ‘dummy’ measure as per your example above. However, Beginning balance and Ending balance are calculated measures. Net change is a real measure. Thanks for the help!

      • I think you need to remove the scope on [Statements].[Statements].Members and just scope on [Measures].[Balance Sheet], [Master Date].[Calendar].MEMBERS and [Statements].[Statements].[Level Four].Members

      • Thanks Chris. That’s what I originally had my scope assignments set to but the calculation stopped working once I associated the parent child hierarchy to the fact table via a many-to-many relationship since i wanted to add another parent-child hierarchy to the same dimension (income statement). I ended up creating two views on top of the physical hierarchy dimension table and relating those to the fact table individually via foreign keys and the scoped assignments evaluated correctly again. Are you aware of issues with scope assignments on a referenced referenced measure via a bridge table? Thanks again for your help.

  44. Sorry about that. I do have the brackets around the scope statements. MDX Studio stripped them out when I formatted the query. The aggregations work fine going up to the first level. The subtotal is only off on the third level subtotal where i’m replacing the measures.

    Balance sheet is a ‘dummy’ measure as per your example above. However, Beginning balance and Ending balance are calculated measures. Net change is a real measure. Thanks for the help!

  45. Hi Chriss,

    I’m thinking to create Measure, purpose is to utilize in powerpivot to make the filter.Suppose I’ve plan year and operation as filter, when I selected “2015 “(Plan Year) and “Division of External Relation”(Operation). The below attributes(Account ID ,AccountName, FromYear, ToYear and Countof MSRP), which are filtered out like based plan year and operation. But, My records like below for sample
    AccountID AccountName FromYear ToYear COuntof MSRP
    1234 abc 1992 2007 124 –>This record has to remove in powerpivot
    1234 cde 2007 4000 124
    Regards,
    Ameer

      • Chris,

        In source side, a two additional attributes(ex: FromYear and Toyear) were added in same dimension(ex: Account Dimension). I added two attributes at existing cube design. While I’m browsing the cube at powerpivot, based on the selection(Ex: PlanYear = 2015) the below structure will replicate
        AccountID AccountName FromYear ToYear COuntof MSRP
        1234 abc 1992 2007 124 –>This record has to remove in powerpivot
        1234 cde 2007 4000 124

        I just wanted to remove the “1234 abc 1992 2007 124 ” record when i was selected 2015 as planyear.

        Please suggest on this requirement.

      • I’m still confused, sorry – are we talking about SSAS here or Power Pivot? Or are you consuming data from SSAS Multidimensional in Power Pivot? If so, why?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s