DAX Series The Calculate Function

The Calculate Function

The CALCULATE() function is one of the most powerful and fundamental functions in Power BI. It is used to modify the evaluation context of a formula, allowing the application of specific filters and conditions to calculation processes. Harnessing CALCULATE() empowers data professionals to create sophisticated and meaningful visualizations and provide data-driven decisions.

Let us understand how it works

The CALCULATE function takes an expression as its first argument, which can be a measure or a calculated column. It then accepts one or more additional arguments, which are the filters or conditions you want to apply. Original evaluation context: The CALCULATE function first evaluates the expression in its original context before applying any filters or modifications. This is essential to understand how the values are being calculated without considering additional filters.

Original evaluation context

The first step is to evaluate the expression provided as the first argument of CALCULATE without any additional filters. This creates an initial result or baseline value for the expression.

Filter Modification

Next, if additional arguments (filters) are provided, DAX applies these filters to modify the filter context for the expression. The filters can be single column filters, multiple column filters, or even more complex expressions.

Filter Propagation

Once the filter context is modified, DAX propagates these modified filters to other parts of the expression that reference columns in the same table or related tables. This means that the modified filter context affects other calculations in the expression, creating a dynamic context for the entire calculation.

Expression Recalculation

After the filter context is modified and propagated, DAX recalculates the expression using the modified context. This step takes into account any row-level filters, column filters, or filters from relationships between tables.

Result

The final result of the CALCULATE function is the value of the expression after all the filter context modifications and recalculations have been applied.

Let's see how it works - SUM()

Total Sales Germany =
CALCULATE(
SUM( Financial[Sales] ),
Financial[Country] = "Germany"
)

SUM(Financials[Sales]): This is the expression that is calculated within the CALCULATE function. The SUM function is used to sum the values in the "Sales" column of the "Financials" table. This means that it will add up all the sales recorded in the "Sales" column of the "Financials" table.

Let's see how it works - FILTER()

Total Sales Germany =
CALCULATE(
SUM( Financial[Sales] ),
Financial[Country] = "Germany"
)

Financials[Country] = "Germany": This is the filter applied to the "Financials" table. The expression Financials[Country] refers to the values in the "Country" column of the "Financials" table. The condition = "Germany" specifies that only the records where the "Country" column has the value "Germany" will be included.

Let's see it works - CALCULATE()

CALCULATE(SUM(Financials[Sales]), Financials[Country] = "Germany"): This is where everything is combined. The CALCULATE function takes the expression SUM(Financials[Sales]) and then applies the filter Financials[Country] = "Germany". This means that the sum of sales is calculated only for the records that meet the condition.

Total Sales Germany =
CALCULATE(
SUM( Financial[Sales] ),
Financial[Country] = "Germany"
)

By Vicente Antonio Juan Magallanes -

13th July 2023 - fp20 analytics