Power BI DAX CALCULATE
The CALCULATE Function
CALCULATE is the most important function in DAX.
It calculates an expression, usually a measure, in a changed filter context.
On the previous page you saw that every cell of a visual has a filter context.
With CALCULATE, the formula itself can add filters or replace them.
This page uses the Poke Mart model described in DAX Introduction, with the Total Sales measure from DAX Measures.
Format each new measure as Whole number with a thousands separator.
Syntax
CALCULATE(expression [, filter1, filter2, ...])
| Argument | Meaning |
|---|---|
| expression | The value to calculate. This is usually a measure, like [Total Sales]. |
| filter1, filter2, ... | Optional. One or more filters, like Stores[Region] = "Kanto". Separate them with commas. |
Kanto Sales
Create a measure that shows the sales of the Kanto region only:
- In the Data pane, select the Sales table.
- On the Home tab, select New measure.
- Type the formula below in the formula bar, and press Enter.
Example
Kanto Sales = CALCULATE([Total Sales], Stores[Region] = "Kanto")
Put Kanto Sales in a card.
The card shortens the number to millions. The exact value is:
| Visual | Kanto Sales |
|---|---|
| Card | 3,081,150 |
CALCULATE added one filter, Region = "Kanto", and then calculated Total Sales.
The result is the total of the 910 orders from the two Kanto stores.
Kanto Sales in a Matrix by Region
Now make a matrix with Region in Rows, and Total Sales and Kanto Sales in Values:
| Region | Total Sales | Kanto Sales |
|---|---|---|
| Hoenn | 2,972,000 | 3,081,150 |
| Johto | 3,363,650 | 3,081,150 |
| Kanto | 3,081,150 | 3,081,150 |
| Total | 9,416,800 | 3,081,150 |
Every row shows the Kanto total, even the rows for Hoenn and Johto.
This is not a mistake.
It is how CALCULATE works:
- The Hoenn row has the filter context Region = "Hoenn".
- The CALCULATE filter is on the same column: Region = "Kanto".
- When a CALCULATE filter uses a column that is already filtered, the new filter replaces the old one.
- Total Sales is calculated with Region = "Kanto". The Hoenn filter is gone.
The Total row has no Region filter, so CALCULATE simply adds Region = "Kanto".
You can read the formula as: "Calculate Total Sales, and for this calculation, the Region is Kanto."
It does not matter which region the visual asks for.
Filters on Other Columns Stay
CALCULATE only replaces filters on the columns it names. All other filters stay.
In a matrix by Category, Kanto Sales shows the Kanto sales of each category:
| Category | Total Sales | Kanto Sales |
|---|---|---|
| Balls | 4,447,200 | 1,320,200 |
| Medicine | 4,413,600 | 1,594,000 |
| Status Healers | 556,000 | 166,950 |
| Total | 9,416,800 | 3,081,150 |
The Category filter from each row is kept, and the Region filter is added.
The same happens with Year: in a matrix by Year, Kanto Sales shows 1,456,050 for 2024 and 1,625,100 for 2025.
In a matrix by Store, Kanto Sales is blank for the four stores outside Kanto.
The Goldenrod City row keeps its Store filter, and CALCULATE adds Region = "Kanto".
No order is from Goldenrod City and from Kanto at the same time, so there is nothing to add up.
The two Kanto stores show their own sales: Viridian City 1,414,600 and Cerulean City 1,666,550.
KEEPFILTERS
What if you want the Hoenn and Johto rows to be blank instead?
Wrap the filter in KEEPFILTERS.
It keeps the existing filter and adds the new one, instead of replacing it:
Example
Kanto Sales Keep = CALCULATE([Total Sales], KEEPFILTERS(Stores[Region] = "Kanto"))
| Region | Kanto Sales | Kanto Sales Keep |
|---|---|---|
| Hoenn | 3,081,150 | |
| Johto | 3,081,150 | |
| Kanto | 3,081,150 | 3,081,150 |
| Total | 3,081,150 | 3,081,150 |
In the Hoenn row, both filters apply: Region = "Hoenn" and Region = "Kanto".
No region is both, so the result is blank.
Several Filters
You can give CALCULATE more than one filter.
All the filters apply at the same time (AND):
Example
Kanto Sales 2025 = CALCULATE([Total Sales], Stores[Region] = "Kanto", 'Date'[Year] = 2025)
The two filters are on different tables, Stores and Date.
That is fine: each filter reaches the Sales table through its own relationship.
To allow several values in the same column (OR), use IN with a list in curly braces:
Example
Kanto Johto Sales = CALCULATE([Total Sales], Stores[Region] IN {"Kanto", "Johto"})
The same filter can also be written with the OR operator: Stores[Region] = "Kanto" || Stores[Region] = "Johto".
In a table visual, the three measures show:
| Measure | Filters | Result |
|---|---|---|
| Kanto Sales | Kanto | 3,081,150 |
| Kanto Sales 2025 | Kanto and 2025 | 1,625,100 |
| Kanto Johto Sales | Kanto or Johto | 6,444,800 |
Kanto Johto Sales is the Kanto total (3,081,150) plus the Johto total (3,363,650).
Rules for Simple Filters
A filter like Stores[Region] = "Kanto" is a Boolean filter: for each value, it is either true or false.
It is fast and easy to read, but it has rules:
- It can only use columns from one table.
- It cannot use a measure. For example,
[Total Sales] > 1000000is not allowed as a filter. - It cannot contain another CALCULATE.
For conditions that break these rules, use the FILTER function, on the next page.
What You Learned
- CALCULATE calculates an expression in a changed filter context.
- A CALCULATE filter replaces an existing filter on the same column. That is why Kanto Sales shows the Kanto total in every row of a matrix by Region.
- Filters on other columns, like Category or Year, stay.
- KEEPFILTERS adds the filter to the existing one, instead of replacing it.
- Several filters are combined with AND. Use IN or || for OR on one column.