Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX CALCULATE


Share

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, ...])
ArgumentMeaning
expressionThe 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:

  1. In the Data pane, select the Sales table.
  2. On the Home tab, select New measure.
  3. 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:

VisualKanto Sales
Card3,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:

RegionTotal SalesKanto Sales
Hoenn2,972,0003,081,150
Johto3,363,6503,081,150
Kanto3,081,1503,081,150
Total9,416,8003,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:

  1. The Hoenn row has the filter context Region = "Hoenn".
  2. The CALCULATE filter is on the same column: Region = "Kanto".
  3. When a CALCULATE filter uses a column that is already filtered, the new filter replaces the old one.
  4. 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:

CategoryTotal SalesKanto Sales
Balls4,447,2001,320,200
Medicine4,413,6001,594,000
Status Healers556,000166,950
Total9,416,8003,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"))
RegionKanto SalesKanto Sales Keep
Hoenn3,081,150
Johto3,081,150
Kanto3,081,1503,081,150
Total3,081,1503,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:

MeasureFiltersResult
Kanto SalesKanto3,081,150
Kanto Sales 2025Kanto and 20251,625,100
Kanto Johto SalesKanto or Johto6,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] > 1000000 is 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.


Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.