You build a simple measure. It adds up sales. It works perfectly, until someone asks, "Can we see that for last year only?" Or "What share of total is this product?" Or "How does it compare to the same period last year?"
Every one of those questions needs the same thing: the ability to change the question a measure is answering. In DAX, that ability has a name: the DAX CALCULATE function.
If you learn one function in DAX, make it this one. It is the engine behind time intelligence, percent-of-total calculations, rankings, dynamic segmentation, and most of the "how did they do that?" measures you see in polished reports.
In this DAX CALCULATE tutorial you will learn:
- What CALCULATE does and how its syntax works
- How filter context, row context, and context transition fit together
- How CALCULATE overrides, removes, and preserves existing filters
- 50 practical CALCULATE examples, from a one-line filter to expert-level patterns
- The mistakes that trip up even experienced developers, and how to avoid them
Everything here applies to CALCULATE in Power BI as well as SSAS Tabular and Azure Analysis Services models.
What Is the DAX CALCULATE Function?
CALCULATE evaluates an expression in a modified filter context. In plain terms: you give it a calculation, such as a sum of sales, plus instructions for changing the filters, and it runs the calculation under those new filters.
CALCULATE syntax
CALCULATE (
<expression>,
[<filter1>],
[<filter2>],
...
)- expression: the calculation to perform. It must return a single (scalar) value, such as a measure or an aggregation like SUM.
- filter arguments: optional. Each one adds, replaces, or removes filters before the expression is evaluated. There can be as many as you need.
The result is whatever the expression returns, computed under the filters you specified.
Why CALCULATE matters so much
CALCULATE and its sibling CALCULATETABLE are the functions built to modify filter context. Nearly every advanced DAX pattern depends on that ability. There is also a less obvious reason: every time you reference a measure inside another expression, DAX silently wraps it in a CALCULATE. That implicit wrapper is what powers context transition, which we cover below.
Understanding CALCULATE means understanding how DAX thinks. So let's start with the core concepts.
Core Concepts You Need Before the Examples
Filter context: the "current set of filters"
Filter context is the set of filters active when a DAX expression is evaluated. It comes from many places: slicers, report filters, rows and columns of a visual, and filter arguments inside CALCULATE.
Imagine a matrix with Category on rows and Year on columns. For the cell at "Bikes / 2024", the filter context is Category = Bikes and Year = 2024. A measure like [Total Sales] sees only the sales rows that survive those filters. The same measure returns a different number in every cell because the filter context is different in every cell.
Row context: "the current row"
Row context means DAX is working through a table one row at a time. You get a row context in two places: calculated columns, and iterator functions such as SUMX, AVERAGEX, and FILTER.
-- Row context inside an iterator
Total Margin Amount =
SUMX (
Sales,
Sales[SalesAmount] - Sales[Cost]
)Here SUMX steps through each Sales row and evaluates the expression with that row's values available. A row context lets you reference columns of the current row. It does not filter the model. That distinction matters in the next section.
Context transition: turning a row into filters
Context transition is what happens when CALCULATE (or a measure reference, which is an implicit CALCULATE) is evaluated inside a row context. DAX converts the current row into equivalent filters on the columns of that row's table, and the expression is then evaluated under those filters.
Sales per Product Average =
AVERAGEX (
Product,
[Total Sales]
)For each Product row, referencing [Total Sales] triggers context transition. The current product row becomes a filter, so [Total Sales] returns the sales of that product. AVERAGEX then averages those values. Without context transition, [Total Sales] would return the same grand total for every row.
Two details to remember:
- The transition filters by all columns of the iterated table. If the table has duplicate rows, the filter matches all of the duplicates.
- It is powerful but can be expensive on large tables, because it runs once per iterated row.
How CALCULATE modifies filters: override, remove, preserve
When you pass a filter argument to CALCULATE, it interacts with the existing filter context in one of three ways:
- Override (the default for column filters): a filter on a column replaces any existing filter on that same column. If a slicer selects Clothing and your measure says
Product[Category] = "Bikes", the measure returns Bikes. - Remove: modifiers such as ALL, ALLEXCEPT, ALLSELECTED, and REMOVEFILTERS clear filters instead of adding them.
- Preserve: KEEPFILTERS makes the new filter intersect with the existing one instead of replacing it.
Filters on different columns combine with AND logic. Filters on the same column are replaced, not combined, unless you use KEEPFILTERS.
The three kinds of filter arguments
1. Boolean expressions
Product[Category] = "Bikes"This is the simplest form. A Boolean filter can reference only one column, and it cannot contain a measure. Behind the scenes, DAX treats it as a filter over the distinct values of that column, so it overrides any existing filter on that column.
2. Table expressions
FILTER ( ALL ( Product[Brand] ), Product[Brand] <> "Contoso" )A table expression returns a table, and its values become filters on the columns it contains. Table filters are more flexible than Boolean filters because they can use measures, multiple columns, and complex logic. The trade-off is that they can be slower when they iterate a large table.
3. Filter modifier functions
These change how filters behave rather than adding a filter of their own: ALL, ALLEXCEPT, ALLSELECTED, REMOVEFILTERS, KEEPFILTERS, USERELATIONSHIP, and CROSSFILTER. Several of them can only be used inside CALCULATE (or CALCULATETABLE). REMOVEFILTERS is one example.
Evaluation order in plain language
The order in which CALCULATE does its work explains many "why did that return the wrong number?" moments. At a conceptual level:
- The filter arguments are evaluated in the original filter context, meaning the context the measure was called in, before any changes are made.
- If a row context exists, context transition converts it into filters.
- Relationship modifiers (USERELATIONSHIP, CROSSFILTER) take effect, and filter-removal modifiers (the ALL family) clear the filters they target.
- The new filters are applied, overriding or intersecting with what remains.
- Finally, the expression is evaluated in the resulting filter context.
The practical takeaway: filter arguments see the outer context, while the expression sees the new context. The order in which you list filter arguments does not change the result, but nesting one CALCULATE inside another does, because the inner one is evaluated inside the outer one's new context.
The Sample Data Model Used in All 50 Examples
Every example uses the same simple star schema, so you can focus on the DAX instead of relearning the model each time.
- Sales (fact table): OrderID, ProductKey, CustomerKey, OrderDate, Quantity, SalesAmount, Cost. Each row is an order line, and Cost is the total cost of that line.
- Product: ProductKey, Category, Subcategory, Brand
- Customer: CustomerKey, Country, Segment
- Date: a proper date table with one row per day, marked as a date table. We assume it has the columns Date, Year, MonthNumber, and MonthName. Sales[OrderDate] has an active relationship to 'Date'[Date].
The relationships run one-to-many from Product, Customer, and Date into Sales. Category names such as "Bikes", "Accessories", and "Clothing", and values such as "United States" and "Corporate", are illustrative. Swap in your own.
Base measures (defined once, reused everywhere)
Total Sales =
SUM ( Sales[SalesAmount] )
Total Cost =
SUM ( Sales[Cost] )
Total Quantity =
SUM ( Sales[Quantity] )
Total Profit =
[Total Sales] - [Total Cost]
Profit Margin % =
DIVIDE ( [Total Profit], [Total Sales] )
Order Count =
DISTINCTCOUNT ( Sales[OrderID] )A quick note on naming: build small, reusable base measures like these and let advanced measures reference them. It keeps formulas short, readable, and consistent.
Beginner CALCULATE Examples (1–12)
These examples cover the foundation: applying a filter, combining filters, and removing filters. If you are new to CALCULATE, work through these in order.
Example 1: Filter by a Single Value
Scenario: Management wants a card showing sales for the Bikes category only, regardless of what the rest of the report shows for category.
Bikes Sales =
CALCULATE (
[Total Sales],
Product[Category] = "Bikes"
)How it evaluates: CALCULATE takes the current filter context, replaces any filter on Product[Category] with "Bikes", and then evaluates [Total Sales]. Filters on other columns, such as Year or Country, stay in place. This is the simplest and most common use of CALCULATE.
Example 2: Filter by Country
Scenario: The US sales team needs its own sales number on a shared dashboard.
US Sales =
CALCULATE (
[Total Sales],
Customer[Country] = "United States"
)How it evaluates: The filter on Customer[Country] travels through the relationship from Customer to Sales, so only sales to US customers are included. Filters on a dimension table propagate to the fact table automatically, so you never need to touch the Sales table directly.
Example 3: Multiple Filters (AND Logic)
Scenario: You want sales of Bikes to Corporate customers only.
Corporate Bikes Sales =
CALCULATE (
[Total Sales],
Product[Category] = "Bikes",
Customer[Segment] = "Corporate"
)How it evaluates: Each filter argument applies to a different column, so they combine with AND. Only sales rows that match both Bikes and Corporate are included. The same applies to as many filters as you add.
Example 4: Multiple Values for One Column (OR Logic)
Scenario: A report needs sales for Bikes and Accessories combined.
Bikes and Accessories Sales =
CALCULATE (
[Total Sales],
Product[Category] IN { "Bikes", "Accessories" }
)How it evaluates: The IN operator turns the filter into "Category is any of these values". Listing two separate Boolean filters on the same column would not work, because the second would replace the first. IN is the clean way to express OR for one column.
Example 5: Excluding a Value
Scenario: Leadership wants to see sales without the Accessories category, which behaves differently from the core business.
Sales Excluding Accessories =
CALCULATE (
[Total Sales],
Product[Category] <> "Accessories"
)How it evaluates: The filter keeps every category value except "Accessories" and replaces any existing filter on Category. Note that this replacement means a slicer that selects only Bikes would be overridden, and the measure would return all categories except Accessories. If you want the exclusion to respect the slicer, you need KEEPFILTERS, which appears in the intermediate section.
Example 6: Filtering on a Numeric Column
Scenario: The operations team wants to know how much revenue comes from bulk order lines, defined as five or more units.
Bulk Line Sales =
CALCULATE (
[Total Sales],
Sales[Quantity] >= 5
)How it evaluates: The Boolean filter selects every Quantity value that is 5 or higher, which in turn selects the matching rows in the Sales table. [Total Sales] then adds up only those rows. Comparison operators work in Boolean filters just like equality does.
Example 7: Overriding the Date Filter with a Fixed Year
Scenario: A KPI card must always show 2024 sales as a fixed benchmark, even when users change the year slicer.
Sales 2024 =
CALCULATE (
[Total Sales],
'Date'[Year] = 2024
)How it evaluates: The filter on 'Date'[Year] replaces whatever year the user selected. Other filters, such as Category or Country, still apply. This override behavior is the basis for most time-comparison measures.
Example 8: Removing a Filter with ALL
Scenario: You need the total sales across all categories, even inside a visual that splits sales by category.
Sales All Categories =
CALCULATE (
[Total Sales],
ALL ( Product[Category] )
)How it evaluates: ALL removes any filter on Product[Category] before [Total Sales] is evaluated. In a visual with a row per category, every row now shows the same all-categories total. Filters on other columns, such as Year or Country, are still respected.
Example 9: Percent of Total Across Categories
Scenario: A bar chart should show each category's share of overall sales.
Category Share of Sales % =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALL ( Product[Category] ) )
)How it evaluates: The numerator uses the current context, so it is the sales for each category. The denominator removes the Category filter and returns the all-category total. DIVIDE returns the ratio and handles division by zero safely. Because the denominator only clears Category, a Year slicer still limits both numbers consistently.
Example 10: Removing All Filters from a Table with REMOVEFILTERS
Scenario: You need total sales regardless of any customer-related filter, so you can compare a selected customer group against the whole customer base.
Sales All Customers =
CALCULATE (
[Total Sales],
REMOVEFILTERS ( Customer )
)How it evaluates: REMOVEFILTERS clears filters from every column of the Customer table, including Country and Segment. Filters from other tables, such as Product and Date, stay active. REMOVEFILTERS is the more readable modern way to express "clear filters" and can only be used as a CALCULATE modifier.
Example 11: Using CALCULATE with Any Measure
Scenario: You want the profit margin for Bikes only.
Bikes Profit Margin % =
CALCULATE (
[Profit Margin %],
Product[Category] = "Bikes"
)How it evaluates: The expression is a measure, not a column aggregation. When CALCULATE changes the filter context, every measure referenced inside [Profit Margin %] sees the new filter. Total Profit and Total Sales are therefore both computed for Bikes before the ratio is taken. This is why small, reusable base measures pay off.
Example 12: Counting Distinct Orders with a Filter
Scenario: The brand manager for Contoso wants to know how many distinct orders included a Contoso product.
Contoso Orders =
CALCULATE (
[Order Count],
Product[Brand] = "Contoso"
)How it evaluates: The filter on Product[Brand] flows to Sales, leaving only order lines for Contoso products. DISTINCTCOUNT on OrderID then counts each order once, even if it contains several Contoso lines. CALCULATE works with any aggregation, not just SUM.