Contact us

Send
inqury

Knowledge base

home banner

bi@unija.com

  • Knowledge

  • Categories

October 23, 2020

DAX function CALCULATE

Author: Branka Trifunović

For Power BI version 2.76.5678.782 (December 2019).

CALCULATE() function is the most important function in DAX language. It’s almost the only function that can change the filter context coming from visualizations. You can read more on filter context in articles Filter Context in Power BI and Context Transition in Power BI.

So how does CALCULATE() function work? Let’s suppose we make an order of office supplies every month.

Standard order of supplies contains: paper, pens, folders, envelopes and paper clips.

While making an order we realize we still have paper clips in storage, so we don’t need to order more. When making an order we say: “Standard order please, without the paper clips”. This is similar to what CALCULATE() function does. It filters the table and calculates the result. We can use none, one or more filters. Functions changes the filter context, which means filters propagate through connections and filter connected tables, all before evaluating and returning the result.

Syntax

CALCULATE(Expression, Filter1, Filter2, Filter3, …)

Parameter Description
Expression Expression to be filtered
Filter1, Filter2, Filter3, … Filters

Example: filtering one table

Supplies table contains the quantity of standard order of office supplies.

We’d like to know how many pieces of office supplies we’ll order if we know we’ll order everything except for paper clips. We follow the steps bellow.

1. We create a measure using the formula below.

CALCULATE_Example = CALCULATE(SUM(Supplies[Quantity]), Supplies[Name] <> “Paper clips”)

2. We use the measure in Matrix visualization. The answer is – as expected – 1300.

Example: filtering second table

Filters can also reference other tables. For example, we want to see the income of products in the Board games group.

We use the following example:

CALCULATE_Example = CALCULATE(SUM(Sales[Amount]), Product[Genre] = “Board games”)

Formula is executed in following steps.

  1. Table Products is filtered, leaving rows that have Board games value in column Genre.
  2. Filter then propagates to the connected table Sales, also leaving only the rows with Board games value in column Genre.
  3. Sum of the Amount column is calculated in the filtered Sales table.

Example: using multiple filters

We can use any number of filters. For example, we want to see income of products in the Board games group in UK, in year 2019.

We use the following formula:

CALCULATE_Example = CALCULATE(SUM(Sales[Amount]), Product[Genre] = “Board games”, Location[Name] = “UK”, Calendar[Year] = 2019)

Formula is executed in following steps.

  1. Table Products is filtered, leaving rows that have value Board games in column Genre.
  2. Table Location is filtered, leaving rows that have value UK in column Name.
  3. Table Calendar is filtered, leaving rows that have value 2019 in column Year.
  4. Filters than propagate to the Sales table, leaving only rows that weren’t filtered out.
  5. Sum of the Amount column is calculated in the filtered Sales table.

 

Index