DAX CALCULATE Explained With 6 Examples
· 7 min read
DAX CALCULATE evaluates an expression (usually a measure) after changing the filter context. You pass it the expression first, then one or more filters. Each filter replaces any existing filter on the same column, and all filters are combined with AND.
Completed Revenue =
CALCULATE ( [Revenue], Sales[Status] = "completed" )That one line is the most common use of the CALCULATE function: a sum with a filter. Below are six examples on a small Brightline Supply Co. model, the numbers they return, and the two filter mistakes AI assistants make most often.
The example data
Brightline's model is a star schema: a Sales fact table, plus Products, Customers and Date dimensions. Here are eight order lines. Category comes from Products through the ProductID relationship.
| OrderID | Category | Channel | Status | Line Revenue |
|---|---|---|---|---|
| 50001 | Coffee & Kitchen | Online | completed | 96.00 |
| 50002 | Paper & Print | Sales Rep | completed | 49.50 |
| 50003 | Cleaning | Online | completed | 75.00 |
| 50004 | Paper & Print | Partner | completed | 112.10 |
| 50005 | Cleaning | Sales Rep | cancelled | 84.00 |
| 50006 | Coffee & Kitchen | Online | completed | 99.50 |
| 50007 | Cleaning | Partner | completed | 180.00 |
| 50008 | Coffee & Kitchen | Online | refunded | 48.00 |
The base measure counts every status:
Revenue =
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) )Example 1: CALCULATE sum with filter
Brightline only counts completed orders as revenue. The measure at the top does that. A filter like Sales[Status] = "completed" is shorthand for a table filter:
Completed Revenue =
CALCULATE (
[Revenue],
FILTER ( ALL ( Sales[Status] ), Sales[Status] = "completed" )
)| Category | Revenue | Completed Revenue |
|---|---|---|
| Coffee & Kitchen | 243.50 | 195.50 |
| Paper & Print | 161.60 | 161.60 |
| Cleaning | 339.00 | 255.00 |
| Total | 744.10 | 612.10 |
The category filter from the visual stays in place. CALCULATE only touched the Status column.
In SQL this is a WHERE clause, or SUM(CASE WHEN status = 'completed' THEN revenue END) if you need both numbers side by side. See CASE WHEN in SQL.
Example 2: CALCULATE with multiple filters
Separate filter arguments are combined with AND:
Online Completed Revenue =
CALCULATE ( [Revenue], Sales[Status] = "completed", Sales[Channel] = "Online" )Result: 270.50 (orders 50001, 50003 and 50006). Order 50008 is Online but refunded, so it drops out.
Example 3: OR conditions
For OR on one column, use IN (or || inside a single condition):
Billed Revenue =
CALCULATE ( [Revenue], Sales[Status] IN { "completed", "refunded" } )Result: 660.10 (612.10 completed plus 48.00 refunded).
For OR across two different columns, filter a table of both columns:
Online Or Completed =
CALCULATE (
[Revenue],
FILTER (
ALL ( Sales[Status], Sales[Channel] ),
Sales[Status] = "completed" || Sales[Channel] = "Online"
)
)Result: 660.10 again, since the only non-completed Online order is 50008 (48.00).
Example 4: CALCULATE with ALL for % of total
ALL used as a CALCULATE filter removes filters. That gives you a denominator for percent of total:
% of Completed Revenue =
DIVIDE (
[Completed Revenue],
CALCULATE ( [Completed Revenue], ALL ( Products[Category] ) )
)| Category | Completed Revenue | % of Completed Revenue |
|---|---|---|
| Coffee & Kitchen | 195.50 | 31.9% |
| Paper & Print | 161.60 | 26.4% |
| Cleaning | 255.00 | 41.7% |
| Total | 612.10 | 100.0% |
REMOVEFILTERS ( Products[Category] ) does the same thing and reads more clearly. Use ALLSELECTED ( Products[Category] ) instead if the percentages should add up to 100% of what a slicer leaves visible. The same idea in a spreadsheet is in percentage of total in Excel.
Example 5: KEEPFILTERS
A CALCULATE filter overwrites any filter on the same column. Put Sales[Status] on the rows and the overwrite becomes visible:
Completed Revenue (keep) =
CALCULATE ( [Revenue], KEEPFILTERS ( Sales[Status] = "completed" ) )| Status | Revenue | Completed Revenue | Completed Revenue (keep) |
|---|---|---|---|
| cancelled | 84.00 | 612.10 | |
| completed | 612.10 | 612.10 | 612.10 |
| refunded | 48.00 | 612.10 | |
| Total | 744.10 | 612.10 | 612.10 |
Without KEEPFILTERS, the "cancelled" row shows completed revenue, because CALCULATE replaced the row's Status filter. KEEPFILTERS intersects the two filters, so cancelled and refunded rows are blank. The same thing happens when a user picks a Status in a slicer.
Example 6: FILTER with a measure condition
A boolean filter cannot use a measure. When the condition depends on an aggregate, iterate a table with FILTER:
Revenue From Big Customers =
CALCULATE (
[Completed Revenue],
FILTER ( Customers, [Completed Revenue] > 150 )
)Completed revenue by customer is Harbor & Pine Hotels 208.10, River Print Co 180.00, Lakeside Studios 149.00 and Pioneer Studios 75.00. On a card, the measure returns 388.10 (Harbor plus River).
Split by category, the answer changes, and this surprises people:
| Category | Revenue From Big Customers |
|---|---|
| Coffee & Kitchen | |
| Paper & Print | |
| Cleaning | 180.00 |
| Total | 388.10 |
The FILTER runs inside each cell's filter context. In the Coffee & Kitchen row, Harbor's coffee revenue is only 96.00, so no customer passes the 150 test. If you mean "customers above 150 in total", remove the category filter inside the condition, for example CALCULATE ( [Completed Revenue], REMOVEFILTERS ( Products ) ) > 150.
How CALCULATE works, step by step
- Start from the filter context of the current cell (rows, columns, slicers, page filters).
- Evaluate each filter argument in that original filter context.
- If CALCULATE runs inside a row context, such as a calculated column or an iterator, turn the current row into filters. This is called context transition.
- Apply
ALLandREMOVEFILTERS, then the filter arguments: each one replaces existing filters on the same columns, unless wrapped in KEEPFILTERS. - Evaluate the expression in the new filter context.
Step 3 is why CALCULATE ( SUM ( Sales[Quantity] ) ) in a calculated column on Customers returns each customer's quantity, while plain SUM ( Sales[Quantity] ) returns the grand total on every row. More on that in measure vs calculated column.
Common mistakes
Removing the wrong filter for % of total
A common AI-written measure:
% of Total =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], ALL ( Products[ProductName] ) ) )With Category on the rows, every category shows 100%. ALL removed filters from ProductName, but the visual filters Category, and that filter is still there, so the denominator equals the numerator. How to spot it: check that the column inside ALL (or REMOVEFILTERS) is the column, or the table, on your visual. A total row of 100% with every row also at 100% is the giveaway.
Using FILTER over the whole fact table
AI assistants often write:
Completed Revenue =
CALCULATE ( [Revenue], FILTER ( ALL ( Sales ), Sales[Status] = "completed" ) )This returns 612.10 on every category row. ALL ( Sales ) returns every Sales row, including the columns of tables Sales is related to, so this filter overrides the Category filter from the visual. Filter the column, not the table: Sales[Status] = "completed". If a measure shows the same number on every row of a matrix, look for ALL on a fact table.
Expecting text comparisons to be case-sensitive
DAX compares text without case: "Completed" matches "completed". Power Query does not, which matters when you filter during data load. See DAX vs Power Query.
FAQ
What does the CALCULATE function do in DAX?
It evaluates an expression in a modified filter context. You add, replace or remove filters, and CALCULATE computes the expression under those filters.
How do I calculate a sum with a filter in DAX?
CALCULATE ( SUM ( Sales[Line Revenue] ), Sales[Status] = "completed" ). Add more conditions as extra arguments for AND, or use IN { } for several values of one column.
What is the difference between CALCULATE and FILTER?
FILTER is an iterator that returns a table. CALCULATE changes the filter context and evaluates an expression. You often pass FILTER into CALCULATE when a condition needs a measure.
What does CALCULATE with ALL do?
It removes filters from the columns or table you name, so the expression is computed as if the visual did not filter them. That is the standard way to get a grand total for a percentage.
Can I use a measure inside a CALCULATE filter?
Not in a simple boolean filter. Wrap it in FILTER over a table, such as FILTER ( Customers, [Completed Revenue] > 150 ).
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Online revenue next to total revenue
- Audit: bulk discount revenueAudit
- Audit: indirect revenue on the wrong rowsAudit