CALCULATE
Power BI · DAX (Data Analysis Expressions)
Theory
CALCULATE evaluates an expression in a modified filter context, and it is the most important function in DAX. Its first argument is the expression, usually a measure; the remaining arguments are filters that are added to, or replace, the filters coming from the visual. A simple filter such as Sales[Region] = "North" replaces any existing filter on that column, so the result is always North's figure regardless of what the visual row says about Region, while filters on other columns, like Category, still apply.
For example, North Sales = CALCULATE([Total Sales], Sales[Region] = "North") in a table by Category returns ₹1,200 for Electronics (order 101) and ₹2,000 for Home (orders 103 and 106), next to Total Sales of ₹4,200 and ₹2,000. Filter arguments can also be modifiers such as ALL, REMOVEFILTERS, KEEPFILTERS or USERELATIONSHIP, or table expressions built with FILTER.
Why we use it
Most business measures are 'X, but for Y': sales for North, sales last year, sales ignoring the region slicer. CALCULATE is how DAX expresses all of them.
When to use it
Use CALCULATE whenever a measure needs a different filter than the visual provides. Use simple column predicates where possible; wrap them in KEEPFILTERS if you want to intersect with the existing filter instead of replacing it.
How it works
- Measure with CALCULATE is evaluated
- Start from the visual's filter context
- Does a filter argument target a column already filtered? Yes: Replace that column's filter. No: Add the filter alongside.
- Apply modifiers such as ALL or USERELATIONSHIP
- Evaluate the expression in the new context
- Return the result for the cell
Syntax
CALCULATE(<expression>[, <filter1>[, <filter2>[, …]]])
Filter forms: Table[Column] = value | Table[Column] IN {…} | FILTER(table, condition)
Modifiers: ALL, ALLEXCEPT, REMOVEFILTERS, KEEPFILTERS, USERELATIONSHIP, CROSSFILTERExample
North Sales =
CALCULATE(
[Total Sales],
Sales[Region] = "North"
)
North Electronics = CALCULATE([Total Sales], Sales[Region] = "North", Products[Category] = "Electronics") // 1,200
// Keep the visual's Region filter and add North on top:
North Only If Selected = CALCULATE([Total Sales], KEEPFILTERS(Sales[Region] = "North"))Key points
- CALCULATE and CALCULATETABLE are the functions that modify filter context
- A column filter replaces existing filters on that column
- Multiple filter arguments combine with AND
- KEEPFILTERS intersects with the current filter instead of replacing
- Calling a measure inside a row context triggers context transition