DAX HANDBOOK
6.4 CALCULATE order of evaluation

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article. 

Authors

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Kristian Radoš

Kristian Radoš

Experienced data analyst. Advanced in SQL, PowerApps and M language.

Explanation

CALCULATE has an evaluation order different from most other DAX calculations. We can say that it works from outer to inner CALCULATE statements. Let’s explain this with an example:

SalesMultipleCalculate =
CALCULATE(
    CALCULATE(
        [SalesAmount],
        Sales[Color] = "Black"
    ),
    Sales[Color] = "Blue"
)

Which value would the SalesMultipleCalculate measure return?
a) Blank value
b) 33,702,965 (Black)
c) 10,828,592 (Blue)

The correct answer is B. Following is the explanation and order of evaluation.

SalesMultipleCalculate =
CALCULATE(
    CALCULATE( [SalesAmount], Sales[Color] = "Black" ),
    Sales[Color] = "Blue"
)
  1. We start with an outer CALCULATE (2nd line). Before evaluating the required argument (3rd line of the measure), the optional filter argument of an outer CALCULATE sets the color to “blue”.
  2. After the outer CALCULATE performs its filter modification, the required argument starts to evaluate (3rd line). This argument also has CALCULATE, meaning the inner CALCULATE also waits for its required [SalesAmount] argument to evaluate, but only after the filter argument does its filter modification. This time the modification of the filter argument sets the color to “black”, and the required argument [SalesAmount] is being calculated in the final filter context which consists of black color.

Sugar Syntax with Boolean Expression

The result is obvious in the visual. Why the correct answer is not blank?

It is because of the overwrite feature of DAX CALCULATE arguments combined with the sugar syntax of the Boolean expression. When we remove the sugar syntax from the formula, the formula looks like this:

SalesMultipleCalculate =
CALCULATE(
    CALCULATE(
        [SalesAmount],
        FILTER(
            ALL( Sales[Color] ),
            Sales[Color] = "Black"
        )
    ),
    FILTER(
        ALL( Sales[Color] ),
        Sales[Color] = "Blue"
    )
)

The outer CALCULATE is overwriting the original filter coming from the visual and always sets the color to the value “Blue”. The inner CALCULATE then overwrites the outer filter (which is now set to blue), and sets its value to be always “Black”. That way, no matter which color filters the visual, the calculation will always return the [SalesAmount] of the black color.

CALCULATE final evaluation order

CALCULATE is a very versatile function and can impact any part of the calculation. To be able to use it correctly, you need to understand its execution order. Below you can find a summary of execution steps for every DAX calculation:

  1. Original filter context
    Filters coming from visual and other cross-filtered visuals will form an original filter context.
  2. Model Modifiers
    CROSSFILTER or USERELATIONSHIP functions used as CALCULATE filter arguments can change the filter propagation and direction throughout the data model.
  3. Row 2 filter context transformation arguments
    If CALCULATE is used inside of a row context, it will transform all values from columns used in the row context into filter arguments and they will be applied to a modified filter context. If there is no row context, this step is skipped.
  4. CALCULATE Filter/Remove arguments (for more info on these types of arguments check this article)
    Only after filters coming from row2filter transformation are applied to the filter context, do the CALCULATE arguments start to further modify the context. It is extremely important to understand that these arguments come after transformation and that they can override filters applied through the row2filter transition. All CALCULATE filter arguments are put in AND condition.
  5. Expression
    Once all CALCULATE arguments are applied, the expression is evaluated in the newly created filter context.

To explain the order of evaluation even further, let’s check the following measure and its implications on the result.

TopQTY_Measure =
MAXX(
    VALUES( FactSales[SalesOrderNumber] ),
    CALCULATE(
        [TotalQTY],
        ALL( FactSales[SalesOrderNumber] )
    )
)

We will again focus on the “Albany” city. These are the steps in which the calculation is performed:

  1. Original Filter context = DimCustomer[City]=”Albany”
    Calculation starts and MAXX creates a virtual table of SalesOrders to iterate upon.
    For each row of the SalesOrder iteration the following calculation applies:
    CALCULATE( [TotalQTY], ALL( FactSales[SalesOrderNumber] ) )
  2. Since used in the row context of the MAXX iteration, CALCULATE will first transform all row contexts to filters and they will merge with the original filter context.
  3. Only after row2filter transformation has been applied, CALCULATE filters apply. That’s why the result is giving a wrong figure of 21 (which is the total sum of qty for all Albany SalesOrders). For the first iteration of Albany Sales order the CALCULATE transformed the row value of SO53451 to a filter, but after that CALCULATE argument kicked in and overwrote that filter with filter remover ALL(FactSales[SalesOrderNumber]) ignoring it altogether.

The nested iteration of the MAXX is shown in the picture below. This example is explained in more detail in the Row2Filter Context transition article.

*DAX is much smarter than that, and in this case it will not even perform context transition, but we found this method of explaining to be the most understandable.

Materials

We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

COMMENTS

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

OUR SERVICES

Prefer live training or consultations?

Table of Content

Table of Content

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

DAX HANDBOOK
7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more

DAX HANDBOOK
6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

DAX HANDBOOK
6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

DAX HANDBOOK
6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
0
Would love your thoughts, please comment.x
()
x
Scroll to Top