DAX HANDBOOK
6.1 CALCULATE syntax

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

• DAX Basics (Single table)
• DAX Advanced (Data model)