SCOPING at different granularities in DAX (Part I)

One of the most powerful MDX functions is SCOPE.  With it, one can override cell values at any level of granularity in an SSAS cube and their new custom values will now be considered when returning aggregate results.

It is generally understood you can’t use SCOPE-type functionality in an SSAS Tabular model.  But if we examine the topic a bit closer, we realize that a lot of what is possible with SCOPE statements can also be replicated with DAX expressions.

One of these examples is the ability to manipulate the granularity at which a computation executes.   My colleague Jason Thomas ( blog | twitter ) recently posted a blog entry of a technique in which the expression scope is changed in order to iteratively prototype the correct granularity needed for a business calculation (See his blog entry here). This is the same technique can also be used to solve advanced Last Non Empty scenarios, something we will blog about soon.

Lets look at another example.  In the Adventure Works cube, there is a SCOPE statement in the MDX cube script in which months are evenly assigned the sales amount quota of their parent quarter on the fiscal year 2002:

image

In the cube designer we see that this is necessary in order to have monthly values, as the dimension usage has been defined by tying the ‘Sales Targets’ measure group with the Date dimension at the Year/Quarter level of granularity:

image

When browsing in Excel, we see the output of the behavior as:

image

We can achieve the same behavior on a tabular model by iterating through the distinct list of months in the lookup (dimension) table. To begin with, we load the appropriate tables in the tabular model:

image

Note that I created a hierarchy with custom fiscal year (FY) and fiscal quarter (FQ) calculated columns, in order to match the dimension member caption in the Adventure Works cube. When pivoting the data, without any DAX expressions defined, we get:

image

Notice the sales amount quota is tied to only one month.  This is because DimTime is at the daily level or granularity;  when Fact Sales Quota relates to DimTime, it assigns values on a specific date – however, as these quota values have been defined by the business at the quarter level, the regular sum aggregation at the monthly level does not make much sense.

SCOPE, in the MDX cube script, deals with it by getting the quarter value and dividing it in three, and assigning this new value to each of the months belonging to that quarter. To match the output of MDX SCOPE in a Tabular model, we use the following DAX expression:

Scoping (SUMX):=SUMX(
    VALUES(DimTime[EnglishMonthName]),
    CALCULATE( SUM( FactSalesQuota[SalesAmountQuota]  ) /
            CALCULATE(
                COUNTROWS( VALUES( DimTime[EnglishMonthName] ) ),
                ALL( DimTime[EnglishMonthName] ) ),
        ALL( DimTime[EnglishMonthName] ) )
)

Which yields the following output:

image

This DAX pattern involves using a row-by-row execution using SUMX over a table of distinct values at the desired level of granularity:

AggregateX ( <granularity level>, <expression> )

As you see, the pattern is just using the signature of any of the AggregateX functions.  The only difference is one of perspective:  in our case, we are explicitly leveraging the first argument to change the granularity at which the expression evaluates.  The granularity level is specified by generating a table at the appropriate level desired, whether you use VALUES, SUMMARIZE or even CALCULATETABLE.

This same technique is something Jason and I have explored when tackling interesting analytical scenarios, like the calculation of Last Ever Non Empty, which also uses the same pattern.

Clearly, the same result can be achieved by using other methods in this case.  One of those is by detecting level, as explained in more detail on prior blog entry.  If we apply that technique here, we get:

Scoping (ISFITLERED):=IF(
    ISFILTERED(DimTime[EnglishMonthName]),
        CALCULATE(
            SUM( FactSalesQuota[SalesAmountQuota] ) / 3,
            ALL(DimTime[EnglishMonthName] )
        ) ,
    IF(
        ISFILTERED(DimTime[FQ]),
            SUM( FactSalesQuota[SalesAmountQuota] ) ,
    IF(
        ISFILTERED(DimTime[FY]),
            SUM( FactSalesQuota[SalesAmountQuota] )
        )
    )
)

image

Notice how the total level is not showing a value, as I didn’t account for that level in my conditional IF statements. This is not ideal as in many scenarios you need the ability to compute on a level that is not displayed on the pivot table or report.

For example, if we wanted to slice this measure by  the Sales Territory Group dimension, we would get:

image

As you can see, when an unexpected context has been introduced, the technique using SUMX is still able to compute at other levels whereas using ISFILTERED can’t due to the unexpected filter which is not accounted for. Clearly, using ISFILTERED in this case would diminish the usability of the model.

ANATOMY OF THE CALCULATION

As we are computing rows for each member at a specific level or granularity, it is necessary to iterate through this members. The SUMX function allows us to do just that.

It is known that iterative functions like this do not perform as well as other calculations that compute on ‘bulk’ mode (like SUM).  However, SUM can only take a column for parameter, whereas with SUMX we can specify a table on which the expression will be computed on a row-by-row basis. An we need this table parameter precisely because it is the one that allows us to define the grain at which we the evaluation to be evaluated.

The calculation takes the list of distinct months and, through row context, grabs the quarter value and divides it by the count of months belonging to that quarter.  This is possible due to the fact that the context at the month level has been cleared out, and hence the values always belong to  the next level for which there is context (quarter in this case):

image

Notice also that the distinct list of months must be generated off the appropriate side of the one-to-many relationship:  if we had a column for month on the fact table and used that we would have gotten the following, obviously incorrect results:

Scoping (Incorrect Grain):=SUMX(

   VALUES(FactSalesQuota[FactMonth]),

    CALCULATE(

        SUM( FactSalesQuota[SalesAmountQuota]  ) / CALCULATE( COUNTROWS( VALUES( DimTime[EnglishMonthName] ) ), ALL( DimTime[EnglishMonthName] ) ),

        ALL( DimTime[EnglishMonthName] )

    )

)

image

Clearly, values not assigned on all qualifying months – only the ones active based on query context. As I have described on a prior blog post, DAX context does not automatically propagate from the base table to the lookup table.

We can take advantage of this by generating the distinct list of months based on the lookup table: doing so guarantees we will get a list of all months, regardless of the current filters in action on the fact (base) table.

IS THIS A REAL ASSIGNMENT OF VALUES?

It is not. In DAX, we are not really assigning or overriding values as we would with an MDX scope statement.  However, in Tabular, we have the ability to generate a new calculation that computes at a certain level of grain; this allows us to mimic the same behavior as that achieved through MDX.

The experienced MDX or DAX user may have noticed that our defined calculation expects a hierarchy in order to show the assigned values: we do this as we are clearing some filters but expecting others to be kept. When looking at the results at one level of grain only (monthly), this will lead to different results to those we get by use of the MDX SCOPE function. However, using DAX we are able to overcome this: see how on the second blog entry on this topic available here: http://javierguillen.wordpress.com/2012/05/04/scoping-at-different-granularities-part-ii/

About these ads

7 Responses to SCOPING at different granularities in DAX (Part I)

  1. Marco Russo says:

    Nice post!
    A possible issue with this type of calculation in tabular model is what happens if the end user select EnglishMonthName below Year, without using the Fiscal Calendar hierarchy. This is really an area where losing attribute relationships make things harder to control. We can just try to mimic the behavior, but in this area (allocation) Multidimensional+MDX is far superior.

    • javierguillen says:

      Yes, definitely. Interestingly, I describe a variation of the calculation that can overcome this issue (of placing the month below the year) on the second part of the blog entry, that I just published here: http://javierguillen.wordpress.com/2012/05/04/scoping-at-different-granularities-part-ii/

      It is still not a perfect match to what MDX assignments can do but what is discussed on the link above gets us a bit closer to the behavior exhibited by SCOPE. One of the interesting applications of this pattern is solving advanced Last Non Empty scenarios; a DAX solution which I am currently testing for scalability

  2. alankoo says:

    Great post!!!

  3. Pingback: SCOPING at different granularities (Part II) « Javier Guillén

  4. Pingback: SCOPING at different granularities (Part III) « Javier Guillén

  5. Hi Javier,

    Hope that you are doing well. I have a problem that I am trying to solve where I want to get a different calculation (perhaps even a different measure) not at a different level of granularity but depending on what the context is at the same granularity level. This seems like the classic use of an IF statement in excel. I can envision it something like IF(“Current Cell Context for Field X” = “Blue”,[Measure X],[Measure Y]). Have you ever worked out anything like this before in your practice?

    Kind Regards,

    John Bradley

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

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: