#### October 23, 2020

## DAX function CALCULATE

*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.

- Table
**Products**is filtered, leaving rows that have**Board games**value in column**Genre**. - Filter then propagates to the connected table Sales, also leaving only the rows with
**Board games**value in column**Genre.** - 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.

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