CALCULATE DAX Function in Power BI

The CALCULATE DAX function is used to modify the filter context by adding or removing filters or by modifying standard filter behavior.

DAX Syntax CALCULATE(Expression[, Filter1 [, Filter2 [, …]]])

The function has the following parameters: • Expression or a measure The expression to be evaluated.

The function requires passing in an expression that returns a scalar value and as many filters as we need. The expression can be a measure (which is a named expression) or any expression that can be evaluated in filter context.

• Filter1, Filter2,…(Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions.

The expression used as the first parameter is essentially the same as a measure. Filters can be:

Boolean filter expressions
Table filter expressions
Filter modification functions

Filter behavior in the CALCULATE Function There are two possible standard outcomes occur we you add filter expressions to the CALCULATE function:

If the columns (or tables) aren't in filter context, then new filters will be added to the filter context to evaluate the CALCULATE expression.
If the columns (or tables) are already in filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.

Order of Evaluation The order of evaluation of the parameters of a function is usually the same as the order of the parameter: the first parameter is evaluated, then the second, then the third, and so on. This is always the case for most of the DAX functions, but not for CALCULATE and CALCULATETABLE. In these functions, the first parameter is evaluated only after all the others have been evaluated.

Example: Let’s we have a table with name Sheet1. Create one table visual on the canvas and add the Country and 10th Marks column from the Sheet1 table to visual.

Let’s create a measure with CALCULATE function.

DAX

Calculate Measure = CALCULATE(SUM(Sheet1[10th Marks]), Sheet1[Country]="Australia")

The output of the above dax function is shown below:

As Country column is already in the filter expression of the CALCULATE function, so that filter context is overwritten by the filter expression.

The above dax code in the backend works as the below code:

DAX

Calculate Measure = CALCULATE(SUM(Sheet1[10th Marks]), 
FILTER(
ALL(Sheet1[Country]),
Sheet1[Country]="Australia")
)

Here, the ALL function clears out the filter context on the specified column, and FILTER function is used to replace the entire filter with a new filter context. That is why with the CALCULATE function we are saying that if the column or table is included in the filter expression of the CALCULATE function is already in the filter context of the calculation, then the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.

In the table visual, the Country column does not have any impact on filtering the values of Calculate Measure, because its filter context is ignoring Country column value. But when we add another column to the table let us see the result.

In the above image we can see that the Name column filter applied on the measure Calculate Measure.

Let’s modify our Calculate Measure again. This time we are going to add the Name column also in the filter expression.

DAX

Calculate Measure = 
CALCULATE (
    SUM ( Sheet1[10th Marks] ),
    Sheet1[Country] = "Australia",
    Sheet1[Name] = "Akshay"
)

Here, we are specifying the filter where the Country name is Australia and Name is Akshay. Please note by default these two conditions are ANDed with each other. The output of the above dax code is shown below:

The above dax code can be rewritten as shown below:

DAX

Calculate Measure =
CALCULATE (
    SUM ( Sheet1[10th Marks] ),
    FILTER ( ALL ( Sheet1 ), Sheet1[Country] = "Australia" ),
    Sheet1[Name] = "Akshay"
)

The output of the above dax code is also shown below:

When there are multiple filters, they can be evaluated by using the AND (&&) logical operator, meaning all conditions must be TRUE, or by the OR (||) logical operator, meaning either condition can be true.

DAX

Calculate Measure = 
CALCULATE (
    SUM ( Sheet1[10th Marks] ),
    Sheet1[Country] = "Australia" ||
    Sheet1[Name] = "Akshay"
)

The output of the above dax function is shown below:

Previous Next