CALCULATE and Filter Context
Meera wants two more numbers beside each branch: its revenue from tea alone, and its share of all revenue. Both need a total worked out under different filters from the ones the visual applies — only tea, or every branch at once. CALCULATE is the one DAX function that changes filters, and it ties this whole course together.
The rule
CALCULATE runs an expression under filters you add, replace or remove.
Filter context. Every cell in a visual is calculated under a set of filters that come from its row and column headers, the slicers and the filter pane. In a table by City, the Pune cell's filter context is City = Pune, and the filter flows downhill through the relationship to Sales.
CALCULATE ( expression, filter1, filter2, … ) works in three moves:
- Start from the cell's current filters.
- Apply each filter argument. A filter on a column replaces any existing filter on that same column, and is added alongside filters on other columns.
- Evaluate the expression under the result.
Tea Revenue = CALCULATE ( [Total Revenue], Products[Category] = "Tea" )
Revenue Share =
DIVIDE ( [Total Revenue], CALCULATE ( [Total Revenue], ALL ( Stores ) ) )ALL ( Stores ) removes every filter on the Stores table, so the denominator is always the all-branch total.
| City | Total Revenue | Tea Revenue | Revenue Share |
|---|---|---|---|
| Pune | 240 | 140 | 40% |
| Mumbai | 360 | 320 | 60% |
| Total | 600 | 460 | 100% |
Pune's tea is orders 1001 and 1003 (60 + 80); Mumbai's is orders 1004 and 1006 (120 + 200).
Row context and context transition. Row context — inside a calculated column or an iterator — knows the current row but filters nothing. So a calculated column on Products written as SUMX ( Sales, Sales[Qty] * Sales[Price] ) shows 600 on every product. Wrap it in CALCULATE and the current row is turned into filters, a step called context transition; now each product shows its own revenue: Masala Chai 180, Green Tea 280, Samosa 140. Referring to a measure does the same thing, because DAX wraps every measure reference in an invisible CALCULATE.
The shortcut
For any CALCULATE cell: list the cell's filters, apply the arguments — same column replaces, other column adds — then compute.
The Tea row
The cell's filter is Category = Tea. The argument also sets Category = Tea, which replaces it with itself: 460.
Drills
Answer fast — trust the rule.
In a table by City, what does Tea Revenue show for Mumbai?
In a matrix with Category on rows, what does Tea Revenue show on the Snack row?
What does Revenue Share show for Pune?
What does ALL ( Stores ) do inside CALCULATE?
A calculated column on Products is SUMX ( Sales, Sales[Qty] * Sales[Price] ), with no CALCULATE. What does it show for Samosa?
Remember
- CALCULATE runs an expression under filters you add, replace or remove.
- List the cell's filters; same column replaces, other column adds; then compute.
- Traps: a CALCULATE filter overrides the visual on its column, and row context alone filters nothing.