Analytic Endeavors Design - Copyright 2024-2025 Analytic Endeavors Inc. Unauthorized use prohibited.
~15 min read
How DAX Thinks
Context is Everything
The single most important concept in DAX, and the key to writing formulas that actually work.
DAX Fundamentals series
Scroll to explore
The Mental Model Shift
Why DAX doesn't work the way you think it does
In Excel, formulas point at specific cells. Cell A1 always means A1. In DAX, formulas exist independently. A measure lives in the model, not in a cell. The surrounding environment determines what data it sees, and the same measure returns different results depending on where it's placed.
Excel
A1
B1
C1
A2
B2
C2
A3
B3
C3
Point at cells
vs
DAX
SUM( Trades[Credits] )
Describe the calculation. The environment decides which rows
Key Insight
An Excel formula says "add up these specific cells." A DAX formula says "add up this column," and the filters, slicers, and row position determine WHICH rows are included.
Automatic Filters
Filters from your visuals, slicers, and report layout. Applied before any DAX runs.
Every visual generates a DAX query. These filters shape what data it evaluates.
From the Report
Filter Context
All active filters that determine which rows your measure sees.
Automatic filters plus any CALCULATE modifications combined.
Most Common
Row Context
Created by iterators and calculated columns. Points at one row at a time.
Aggregation functions like SUM ignore row context entirely. CALCULATE bridges the gap.
Calculated Columns
Automatic Filters
Every visual in Power BI generates a DAX query behind the scenes
When you drop a measure into a visual, Power BI writes a DAX query. The report UI automatically applies filters and groupings that shape which data the measure evaluates against. Click each element below to see how each source contributes to the filter context.
Click any element in the report above, or any source chip below, to see how they connect.
Filter Context
The set of filters that narrow what data your measure evaluates
Every time a measure runs, a filter context determines which rows are visible. This context comes from two sources: automatic filters created by the report UI (visuals, slicers, report filters) and explicit filter modifications written in DAX code using CALCULATE. Both work together, and filters stack. Let's trace how Total Revenue = SUM('Trades'[Credits]) arrives at its result.
Step through a simplified report to see how automatic filters (from the report UI) and explicit modifications (from DAX code) combine to produce a single cell value.
Automatic Filters
From the report UI
DAX Modifications
Explicit filters in DAX code (CALCULATE)
Automatic Filters
No active filters
DAX Modifications
No CALCULATE modifications
Total Revenue
SUM ( 'Trades'[Credits] )
Galactic Trades Report
Cycle ▾
2224
2225
2226
Origin ▾
All ▾
Class ▾
All ▾
Table Visual
Cargo
Class
Total Revenue
Warp Revenue
Grand Total
Filters
Filters on this visual
Cargo is not blank(none)
Filters on this page
Route = Warp(none)
Filters on all pages
Cycle ≥ 2224(none)
The report starts with Total Revenue. No filters are active yet.
Step 0 of 6
Key point: Automatic filters (from the report UI) are additive; each layer can only narrow the data further. But CALCULATE is different: it can replace or remove existing filters, not just add to them. This is why CALCULATE is the most important function in DAX. It gives your measures control over the filter context that the report UI alone cannot provide.
CALCULATE
The most important function in DAX. It modifies filter context
CALCULATE takes an expression and runs it in a modified filter context. Add filters below to build your CALCULATE formula and see the result change live.
Filters
Formula
Total Revenue= CALCULATE( [Total Credits] )
Slicer: Cycle = 2225. Each row shows per-product Revenue for 2225. Select a filter to see how CALCULATE adds conditions to the measure.
Table Visual
Cargo
Class
Origin
Revenue
Revenue (Modified)
Result
$1.1M
$1.1M
CALCULATE can also shift existing filters. For example, time intelligence functions like DATEADD take the current date filter and move it to a prior or future period. These patterns are covered in Part 3: Time Intelligence.
Common CALCULATE Patterns
CALCULATE accepts multiple comma-separated filter arguments. Each argument acts as an independent filter, and they combine with implicit AND logic: all conditions must be true. The patterns below cover the most common filter argument shapes.
Use && (or the AND function) to combine two conditions into a single filter argument. Comma-separated arguments handle independent filters; && is for compound conditions within one argument.
IN {} is best practice for matching multiple values on one column. For simple two-value alternatives, || (double pipe) or OR() also work. IN scales cleaner as the list grows.
Clears filters on a specific column or entire table. Accepts a column reference or table name. Use REMOVEFILTERS('Cargo') to clear all columns on the Cargo table at once.
ALL( )
CALCULATE ( [Total Credits], ALL ( 'Cargo' )
)
Removes all filters from a table or column. Common for "% of total" patterns where the denominator ignores slicers.
Restores the "visual total" scope. Removes filters added by the visual itself but keeps slicer and page filters. The go-to for "% of visible total."
Row Context
The hidden context that catches everyone off guard
When you create a calculated column, DAX iterates row by row through the table. Each row creates a row context. But here is the trap: aggregation functions like SUM ignore row context entirely.
Aggregation functions like SUM, AVERAGE, and COUNT are designed to work on entire columns, not individual rows. When SUM runs in a calculated column, it asks one question: "What filters are active?" It does not ask "What row am I on?" This means row context (the current row in a calculated column or iterator) is completely invisible to aggregation functions. They see the filter context only. This is by design: DAX deliberately separates "which row I am reading" from "which rows I should aggregate." Understanding this separation is the key to understanding why CALCULATE exists.
The Row Context Problem
// Calculated column on Cargo table Cargo Revenue=SUM('Trades'[Credits])
Without CALCULATE, row context exists but SUM ignores it. It sums the entire column. Every row gets the grand total of $5.7M in credits.
Cargo
Class
Cargo Revenue
When do you need row context?
• Calculated columns: row context is created automatically
• Iterator functions (SUMX, AVERAGEX, etc.): they create row context per row
• Row context alone does NOT filter. Use CALCULATE to convert it into filter context
// The fix: wrap in CALCULATE Cargo Revenue= CALCULATE( SUM('Trades'[Credits]) )
This is exactly the problem that context transition solves. In the next section, you will see how CALCULATE bridges row context and filter context.
Context Transition
When CALCULATE converts row context into filter context
Imagine you add a calculated column to the Cargo table (a dimension table with 6 rows, one per cargo type). The formula calls SUM on Trades[Credits] (the fact table with 20 transaction rows). You expect each cargo row to show its own total. But SUM ignores which row you are on; it always returns the grand total across all 20 trades. CALCULATE fixes this by converting the current row's values into a filter that the entire model respects. This process is called context transition. This also explains why referencing a measure (like [Total Credits]) in a calculated column works correctly; every measure carries an implicit CALCULATE.
1Row context identifies the current row: CargoName = "Stardust Ore" and all other columns on that row
➔
2CALCULATE converts all these row values into filters applied to the entire model
➔
3Filters flow from Cargo to Trades via relationships, returning $2.1M not $5.7M
When does context transition happen?
• When CALCULATE runs inside an iterator (SUMX, AVERAGEX, etc.)
• When a measure is referenced in a calculated column (implicit CALCULATE)
• Every column of the current row becomes a filter (can be expensive on wide tables)
// These are equivalent: Cargo Revenue=[Total Credits]
// ...is the same as: Cargo Revenue= CALCULATE([Total Credits])
Why does DAX need this? DAX is unique among formula languages in having this two-context system. SQL specifies filters explicitly in WHERE clauses. Excel formulas point at specific cell ranges. DAX formulas describe calculations without specifying which rows to include; the context handles that. Context transition is the mechanism that lets row-level iteration and model-wide aggregation work together. Without it, calculated columns could never show per-row aggregated results.
Iterator Functions
The "X" functions that combine row context with aggregation
Iterator functions (the "X" functions) are most commonly used inside measures. They loop through a table row by row, evaluate an expression for each row, then aggregate the results into a single value. The tables below represent what the engine builds internally during evaluation; you never see these tables in your report. Because iterators produce a single scalar result, the measure works in any visual on any report page. Select a function below to see how the engine iterates.
Table Functions
Measure Definition
Final Result
Engine Table
Internal Engine Table (not visible in your report)
Key Takeaways
The essentials from this guide
DAX Is Context-Driven
The same formula returns different results depending on which filters are active. There are no cell references.
Filter Context Narrows Data
Slicers, filter panes, and visual groupings all add filter layers. Filters are additive; they only shrink the visible data.
Row Context Iterates
Calculated columns and X-functions create row context, pointing at one row at a time. Aggregators like SUM ignore it.
CALCULATE Modifies Filter Context
It can add, remove, or replace filters. It is the most powerful function in DAX.
Context Transition Bridges the Gap
When CALCULATE runs inside a row context, it converts row values into filter context.
X Functions Evaluate Per Row
SUMX, AVERAGEX, and other iterators loop through a table row by row. Any column reference inside resolves to that row's value, making per-row calculations possible.
**On screen:** AE logo, "How DAX **Thinks**: Context is Everything," ~15 min read badge, three-part series nav with Part 2 highlighted.
- Welcome to Part 2 of the DAX Fundamentals series
- Part 1 covered the language; this part covers **how it thinks**
- The single most important concept in DAX: **context**
- After this part: filter context, row context, CALCULATE, context transition, iterators
*Transition:* "Let's start with the mental shift this whole guide is built around."
**On screen:** Section header "The Mental Model Shift." Paragraph about Excel cells vs DAX measures.
- In Excel, formulas point at **specific cells**. A1 always means A1.
- In DAX, a measure lives in the **model**, not in a cell.
- The **surrounding environment** decides what data it sees.
- Same measure, different placement, different result.
*Transition:* "Easiest way to see this is side by side."
**On screen:** Side-by-side "Excel vs DAX" visual. Left: 3x3 cell grid with cells highlighted. Right: a cloud labeled `SUM( Trades[Credits] )`. Key Insight callout below.
- Left side: Excel **points at cells**. The formula names specific addresses.
- Right side: DAX **describes the calculation**. `SUM(Trades[Credits])` says "add this column up." The **environment** decides which rows.
- The key-insight callout nails it: Excel says "add up these specific cells"; DAX says "add up this column," and filters/slicers/row position decide which rows are included.
*Transition:* "There are three different contexts that decide what 'which rows' means. Three concept cards coming."
**On screen:** Card 1: "Automatic Filters." Magnifying glass icon. Tag: "From the Report."
- Filters created by the **report UI**: visuals, slicers, the filter pane
- Applied **before any DAX runs**
- Every visual generates its own DAX query; these filters shape what that query sees
- The pre-game, not part of the formula
**On screen:** Card 2: "Filter Context." Filter funnel icon. Tag: "Most Common."
- The **full set** of active filters at the moment a measure runs
- Automatic filters from the report **plus** any CALCULATE modifications in code
- This is what measures actually see
- "Filter context" is what most people mean when they say "context" in DAX
**On screen:** Card 3: "Row Context." Table-with-active-row icon. Tag: "Calculated Columns."
- Created by **iterators** (SUMX, AVERAGEX, etc.) and **calculated columns**
- Points at **one row at a time**
- The trap: aggregation functions like SUM **ignore** row context completely
- CALCULATE is the bridge between row context and filter context (covered in detail later)
*Transition:* "Let's look at automatic filters first; they're where every measure starts."
**On screen:** Section header "Automatic Filters." Subhead: "Every visual in Power BI generates a DAX query behind the scenes."
- When you drop a measure into a visual, Power BI **writes a DAX query** for you
- The UI **automatically** applies filters and groupings
- The interactive demo below lets you click each filter source and see what it contributes
*Transition:* "Walk through the six sources on screen."
**On screen:** Six clickable chips: Visual Groupings, Slicers, Cross-Highlighting, Visual-Level Filters, Page-Level Filters, Report-Level Filters. Click each to highlight the matching element in the wireframe.
- **Visual Groupings**: the columns/rows on the visual itself (the axis, the legend). Each grouping becomes a filter on its column.
- **Slicers**: explicit user-controlled filters. The familiar one.
- **Cross-Highlighting**: when a user clicks a bar in one chart, every other visual on the page gets that as a filter.
- **Visual-Level Filters**: filters set in the filter pane on this specific visual only.
- **Page-Level Filters**: filters that apply to every visual on the current page.
- **Report-Level Filters**: filters that apply to every page in the report.
- All six combine into one filter context before the measure runs.
*Transition:* "Now the formal term: filter context."
**On screen:** Section header "Filter Context." Subhead about the set of filters narrowing data.
- **Filter context = automatic filters + explicit modifications from DAX code (CALCULATE)**
- Both work together. Filters **stack**.
- The walkthrough below traces `Total Revenue = SUM('Trades'[Credits])` through every step
*Transition:* "Let's step through it."
**On screen:** Interactive "Filter Context Report" demo. Left sidebar shows two boxes ("Automatic Filters" / "DAX Modifications") and the formula. Right side shows a simulated PBI report: slicer for Cycle, table visual with Cargo/Class/Total Revenue/Warp Revenue, and a filter pane with visual/page/report filters. "Step 0 of 6" counter at the bottom.
- Click **Next** to walk through six steps; each step adds a filter and shows what the value collapses to
- Watch the two left sidebar boxes light up: first the automatic-filters box (from slicers and the filter pane), then the DAX-modifications box (from CALCULATE on the Warp Revenue column)
- The grand total in the table footer changes at each step; that's the same `SUM('Trades'[Credits])` formula reacting to a shifting context
- Reset to step 0 before moving on so the next slide starts clean
**On screen:** Orange callout: "Key point."
- Automatic filters are **additive**: each layer can only **narrow** the data further
- **CALCULATE is different**: it can **replace or remove** existing filters, not just add to them
- That asymmetry is why CALCULATE is the most important function in DAX
- It gives measures control the report UI alone can't provide
*Transition:* "So let's look at CALCULATE properly."
**On screen:** Section header "CALCULATE." Subhead: "The most important function in DAX. It modifies filter context."
- CALCULATE takes an **expression** and runs it in a **modified filter context**
- The interactive builder below lets you add or remove filters and watch the result change
*Transition:* "Spin up the builder."
**On screen:** "CALCULATE builder" with two mode tabs: **Add** and **Remove**. Left: filter list (toggleable). Center: live formula. Right: result.
- **Add mode**: click filters to add them as comma-separated arguments to CALCULATE. Each argument acts as an independent filter; they combine with implicit AND.
- **Remove mode**: click filters to clear them with REMOVEFILTERS / ALL. The formula updates to show the right function for the choice.
- Click around live; show how the result number changes as filters add or peel away
- Reset before moving to the patterns walkthrough
**On screen:** Heading "Common CALCULATE Patterns." Intro paragraph about comma-separated arguments combining with AND.
- Six pattern cards coming, one per click
- The unifying idea: **comma-separated filter arguments combine with implicit AND**. All conditions must be true.
- Each card shows the syntax and when to reach for it
**On screen:** Pattern card: "AND / &&". Code: `CALCULATE([Total Credits], 'Cargo'[Class] = "Minerals" && 'Cargo'[Origin] = "Titan")`.
- Use `&&` (or the AND function) for **compound conditions on different columns within a single filter argument**
- Comma-separated arguments handle independent filters; `&&` joins two conditions into one argument
- Both styles produce the same logical AND
**On screen:** Pattern card: "OR / IN". Code: `CALCULATE([Total Credits], 'Cargo'[Class] IN {"Minerals", "Tech"})`.
- `IN {}` is the cleanest way to match **multiple values on a single column**
- For two values, `||` or `OR()` also work
- IN scales better as the list grows; preferred best practice
**On screen:** Pattern card: "REMOVEFILTERS( )". Code: `CALCULATE([Total Credits], REMOVEFILTERS('Cargo'[Class]))`.
- Clears filters on a specific **column or entire table**
- `REMOVEFILTERS('Cargo')` clears every column on Cargo at once
- Modern, explicit name for what older code did with ALL()
**On screen:** Pattern card: "ALL( )". Code: `CALCULATE([Total Credits], ALL('Cargo'))`.
- Removes **all filters** from a table or column
- Classic use: **percent of total**, where the denominator ignores slicers
- Same effect as REMOVEFILTERS in this position; ALL is the older, more familiar name
**On screen:** Pattern card: "ALLEXCEPT( )". Code: `CALCULATE([Total Credits], ALLEXCEPT('Cargo', 'Cargo'[Class]))`.
- Removes **all filters on a table EXCEPT the columns you list**
- Useful for **subtotals that respect one dimension but ignore others**
- Example: "% of Class total" still filtered by Class but ignoring Origin/Value
**On screen:** Pattern card: "ALLSELECTED( )". Code: `CALCULATE([Total Credits], ALLSELECTED('Cargo'[Class]))`.
- Restores the **"visual total" scope**
- Removes filters added by the **visual itself** but **keeps slicer and page filters**
- The go-to for **"% of visible total"**: what the user sees on the page is the denominator
*Transition:* "That's the filter context side. Now the other half: row context."
**On screen:** Section header "Row Context." Two paragraphs about calculated columns and the SUM trap.
- Calculated columns iterate **row by row**; each row creates a **row context**
- The trap: **SUM, AVERAGE, COUNT ignore row context**. They only see filter context.
- DAX deliberately separates "which row I'm reading" from "which rows I aggregate"
- That separation is **why CALCULATE exists**
*Transition:* "Let's see the problem live."
**On screen:** Interactive "Row Context Problem" demo. Three mode buttons: "Without CALCULATE," "With CALCULATE," "Measure Reference." Code block on the left, results table on the right.
- **Without CALCULATE**: every row shows the grand total ($5.7M). Row context exists, but SUM ignores it and sums the whole column.
- **With CALCULATE**: each row shows its own per-cargo total. CALCULATE converts row values into filters.
- **Measure Reference**: same result as With CALCULATE, because **measure references carry an implicit CALCULATE**.
- Click through all three modes; the table values change to match
*Transition:* "So when do you actually need row context?"
**On screen:** Orange callout with two columns. Left: when row context applies. Right: code showing the fix wrapping SUM in CALCULATE.
- Row context is **automatic** in calculated columns
- **Iterators** (SUMX, AVERAGEX, etc.) create row context per row
- **Row context alone does NOT filter.** It just identifies the current row.
- Use **CALCULATE** to convert it into filter context
- This is **context transition**, the next section
**On screen:** Section header "Context Transition." Subhead: "When CALCULATE converts row context into filter context."
- Setup: a calculated column on the **Cargo** table (6 rows, one per cargo type)
- Formula: SUM on **Trades[Credits]** (fact table, 20 rows)
- Expected: each cargo row shows its own total. Actual without CALCULATE: every row shows $5.7M.
- CALCULATE fixes this by **turning the current row's values into filters for the whole model**
- That process is **context transition**
- Bonus: this also explains why referencing a measure works: **every measure carries an implicit CALCULATE**
*Transition:* "Side-by-side comparison coming."
**On screen:** Left panel: "Without Context Transition." Formula: `Cargo Revenue = SUM('Trades'[Credits])`. Table shows every row of Cargo with the same $5.7M value (highlighted red as wrong).
- Same column on every row. The grand total.
- SUM sees the **filter context only**; no filter on Cargo means all 20 trades counted
- This is the broken state we're about to fix
**On screen:** Right panel: "With Context Transition." Formula: `Cargo Revenue = CALCULATE(SUM('Trades'[Credits]))`. Table shows different per-row values: Stardust Ore $2.1M, Plasma Crystals $2.6M, Nav Beacon $91.6K, etc.
- One CALCULATE wrapper changes everything
- Each row now shows its own cargo's total
- The CargoName/Class/Value of the current row became filters applied to Trades through the relationship
- Same SUM expression, same data, totally different result because the context transitioned
**On screen:** Three-step mechanism diagram with numbered circles and arrows.
- **Step 1**: row context identifies the current row. For Stardust Ore, that's CargoName="Stardust Ore" and every other column on that row.
- **Step 2**: CALCULATE converts **all** those row values into filters applied to the entire model.
- **Step 3**: filters flow from Cargo to Trades via the relationship. SUM now sees only Stardust Ore trades. Returns $2.1M instead of $5.7M.
- This is the mechanism behind every per-row aggregation in a calculated column.
**On screen:** Orange callout. Left: when context transition happens. Right: equivalence code showing `[Total Credits]` is the same as `CALCULATE([Total Credits])`.
- **CALCULATE inside an iterator** (SUMX, AVERAGEX, etc.): transition happens for each iterated row
- **A measure referenced in a calculated column**: implicit CALCULATE, transition happens automatically
- **Every column** of the current row becomes a filter; can be expensive on **wide tables**
- The code block makes the implicit/explicit equivalence visible
**On screen:** "Why does DAX need this?" callout. Single paragraph.
- DAX is **unique** in having a two-context system
- SQL uses explicit WHERE clauses; Excel points at ranges; **DAX describes calculations without specifying which rows**
- Context transition is the mechanism that lets **row-level iteration and model-wide aggregation work together**
- Without it, calculated columns could never show per-row aggregated results
*Transition:* "Speaking of iteration, let's look at the X functions."
**On screen:** Section header "Iterator Functions." Subhead: "The 'X' functions that combine row context with aggregation."
- The X family is mostly used inside **measures**
- The pattern: **loop a table row by row, evaluate an expression per row, aggregate the results into one number**
- The tables shown below are what the engine builds internally; you never see them in a report
- Because they collapse to a single scalar, iterator-based measures work in **any visual**
*Transition:* "Tabbed explorer below: 6 X functions plus 2 table functions."
**On screen:** Tabbed explorer. X-function tabs: SUMX, AVERAGEX, MAXX, MINX, COUNTX, RANKX. Table-function tabs: VALUES, DISTINCT. Each tab shows the measure definition, the engine's internal table, the final result, and a best-practice tip.
- **SUMX**: loops a table, evaluates an expression per row, sums the results. The most common iterator. Classic use: `SUMX(Trades, Trades[Units] * Trades[UnitPrice])` for revenue calculated per row.
- **AVERAGEX**: same pattern, averages instead of sums. Useful for "average per day," "average per customer."
- **MAXX / MINX / COUNTX**: same pattern with max, min, count. COUNTX counts non-blank expression results, not just rows.
- **RANKX**: ranks each row's expression result against the rest. The ranking iterator.
- **VALUES** (teal tab): table function. Returns a one-column table of distinct values **currently in filter context**. Common feeder for AVERAGEX/SUMX over distinct dates or distinct categories.
- **DISTINCT** (teal tab): similar to VALUES but ignores blank rows that VALUES would include. Subtle difference, but RANKX often needs DISTINCT.
- Click through 2 or 3 tabs live; the engine table and result update so you can see the iteration mechanism
*Transition:* "Let's wrap with the takeaways."
**On screen:** Section header "Key Takeaways." Subhead: "The essentials from this guide."
- Six takeaway cards, then the bridge to Parts 1 and 3
- Quick recap; the cards are reinforcement, not new material
*Transition:* "One card per click."
**On screen:** Card 1: "DAX Is Context-Driven."
- **Same formula, different filters, different result**
- No cell references; context decides what data the formula sees
- The mental shift from Part 1, now confirmed
**On screen:** Card 2: "Filter Context Narrows Data."
- Slicers, filter panes, visual groupings all add **filter layers**
- Filters are **additive** from the UI side: they only **shrink** the visible data
- (CALCULATE is the exception; it can replace or remove)
**On screen:** Card 3: "Row Context Iterates."
- **Calculated columns** and **X-functions** create row context
- Points at one row at a time
- **Aggregators like SUM ignore row context.** That's the trap.
**On screen:** Card 4: "CALCULATE Modifies Filter Context."
- Can **add, remove, or replace** filters
- The most powerful function in DAX
- Every modern DAX pattern depends on it
**On screen:** Card 5: "Context Transition Bridges the Gap."
- When CALCULATE runs inside a row context, it converts the current row's values into filter context
- Happens **automatically** when a measure is referenced inside an iterator or calculated column (implicit CALCULATE)
- The mechanism behind per-row aggregations
**On screen:** Card 6: "X Functions Evaluate Per Row."
- SUMX, AVERAGEX, and the rest loop a table row by row
- Any column reference inside resolves to **that row's value**
- Makes per-row calculations possible without a calculated column
**On screen:** Two navigation cards: "Back to Part 1" (Understanding DAX) and "Continue to Part 3" (DAX Patterns).
- Part 1 covered the language and the model
- Part 3 takes everything from Part 2 and applies it: time intelligence, variables, inactive relationships, calculation groups, engine internals
- If anything in this part felt theoretical, Part 3 is where it becomes practical patterns you'll reuse for years
*Transition:* "Questions before we move on?"
Discussion