DAX HANDBOOK
6.1 CALCULATE syntax

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.

Syntax

CALCULATE([expression],filter1,filter2,…)
CALCULATE has only 1 required argument, and that is the expression (measure) to evaluate. All the other arguments are optional and are used to modify the filter context in which the required argument will be evaluated. Those modifications lead to a complex and extremely powerful computational power of DAX language. It is extremely important to understand all the ways you can alter the filter context originally impacting any part of the calculation.

First, we will explain the most basic type of filter, the Boolean filter.

[expression] argument is evaluated only after all filter arguments are evaluated.

Boolean Filter Argument

All filter arguments of CALCULATE are put in AND order. If any column from the filter arguments already exists in the original filter context, its values are automatically replaced with the modified ones. This is how DAX modifies the original filter context by default, and this operation is called overwrite.

You can manually change how the modified filter argument interferes with the original one with the use of KEEPFILTERS function. This function changes the original semantic from overwrite to intersect. Following is the explanation of the paragraph using the measure [Blue Sales].

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


When we use column = “X” type of argument (also called a boolean argument) in CALCULATE, we are using a Boolean type of filter, which is a table type of filter (more on this soon). Although we do not see any table or list of new values, there is a hidden ALL table function invoke happening in the DAX. In fact, when using a Boolean filter, DAX introduces sugar syntax, and internally it translates the code above into the following one:

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

Now it is obvious that we are supplying a table consisting of all colors, which are then filtered to the “Blue” one.

Why is it always the same number, no matter which color is filtering the visual? It’s due to the overwrite feature of CALCULATE argument. Following is the evaluation of the Measure in the cell containing the Red filter.

  1. Original filter context consists of only red products.
  2. Since there are no other filters influencing the selected cell, the calculation starts.
  3. CALCULATE needs to evaluate the [SalesAmount], but before it does so, it must apply the filter argument. [SalesAmount] expression will evaluate only after the 4th step.
  4. FILTER(ALL(Sales[Color]), argument returns a table with one column containing all the colors. Next, in the filter argument Sales[Color] = “Blue” DAX iterates over the virtual table with all colors and only the Blue color survives the filter. Since the column Sales[Color] used in CALCULATE filter argument is the same as the one used in the visual, the DAX performs overwrite operation, meaning it ignores the Red color coming from the original filter context and instead returns a Blue filter. It does the same for every cell of the visual.

KEEPFILTERS Function

If, instead of overwrite, we wish to use intersect of the new filters with the original one, we would need to use the KEEPFILTERS function to change the way CALCULATE merges the original filter context with its filter arguments.

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

We will focus on the CALCULATE filter argument KEEPFILTERS( FILTER( ALL( Sales[Color] ), Sales[Color] = “Blue” ))

ALL(Sales[Color]) function returns a table with one column containing all the color values. Next, in the filter argument Sales[Color] = “Blue”, DAX iterates over the virtual table with all colors and only the Blue color survives the filter. Since we use KEEPFILTERS we will change the way DAX joins the original context with the filter arguments, and instead of overwrite we will force intersect, meaning the original filter context will remain active and the CALCULATE filter argument will form the “AND” rule with it. Doing so, only the rows where both original filter context and modified filter context return the same color will survive intersection. In our case that happens only in the cell filtered by the blue color.

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