How DAX Thinks: Context is Everything

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.

Primary Sources of Automatic Filters

Visual Groupings Slicers Cross-Highlighting Visual-Level Filters Page-Level Filters Report-Level Filters Row-Level Security

Cycle

2224

2225

2226

Origin

Class

Cargo

Stardust Ore

Plasma Crystals

Void Silk

Table Visual

Cargo	Origin	Class	Units	Revenue
Stardust Ore	Titan	Minerals	340	$1.0M
Plasma Crystals	Andara	Minerals	220	$661.2K
Nav Beacon	Polar	Tech	600	$30.8K
Cryo Flask	Cobalt	Tech	400	$20.4K
Void Silk	Verdant	Textiles	350	$52.8K
Nebula Thread	Titan	Textiles	250	$37.8K

Revenue by Cargo

+62% $1.0M

Stardust

-41% $661.2K

Plasma

+78% $52.8K

Void S.

+33% $37.8K

Nebula

-67% $30.8K

Nav B.

+80% $20.4K

Cryo

-23% $210.8K

Rift D.

-55% $15.4K

Star K.

Total Revenue

$1.8M 12.4% vs Prior Cycle

MAMJJASONDJF

Avg per Cargo

$303.8K 8.2% vs Prior

MAMJJASONDJF

Cargo Count

6 3.4% vs Prior

MAMJJASONDJF

Filters

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)

Cycle ▾

2224

2225

2226

Origin ▾

Class ▾

Table Visual

Cargo	Class	Total Revenue	Warp Revenue
Grand Total

Filters

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.

AND / &&

                  CALCULATE (

                    [Total Credits],

                    'Cargo'[Class] = "Minerals"

                      && 'Cargo'[Origin] = "Titan"

                  )

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.

OR / IN

                  CALCULATE (

                    [Total Credits],

                    'Cargo'[Class] IN

                      { "Minerals", "Tech" }

                  )

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.

REMOVEFILTERS( )

                  CALCULATE (

                    [Total Credits],

                    REMOVEFILTERS ( 'Cargo'[Class] )

                  )

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.

ALLEXCEPT( )

                  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.

ALLSELECTED( )

                  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."

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.

✗ Without Context Transition

// Calculated column on Cargo (dim) table
Cargo Revenue = SUM( 'Trades'[Credits] )

Cargo	Class	Revenue
Stardust Ore	Minerals	$5.7M
Plasma Crystals	Minerals	$5.7M
Nav Beacon	Tech	$5.7M
Cryo Flask	Tech	$5.7M
Void Silk	Textiles	$5.7M
Nebula Thread	Textiles	$5.7M

✓ With Context Transition

// Calculated column on Cargo (dim) table
Cargo Revenue = CALCULATE( SUM( 'Trades'[Credits] ) )

Cargo	Class	Revenue
Stardust Ore	Minerals	$2.1M
Plasma Crystals	Minerals	$2.6M
Nav Beacon	Tech	$91.6K
Cryo Flask	Tech	$60.8K
Void Silk	Textiles	$158.6K
Nebula Thread	Textiles	$125.6K

1 Row context identifies the current row: CargoName = "Stardust Ore" and all other columns on that row

➔

2 CALCULATE converts all these row values into filters applied to the entire model

➔

3 Filters 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.

Back to Part 1

Understanding DAX

The language, syntax, data model, and how filters flow through relationships.

Part 1 of 3

Continue to Part 3

DAX Patterns: Time, Variables, and Beyond

Time intelligence, variables, inactive relationships, calculation groups, and how the engine works.

Part 3 of 3

How DAX Thinks

The Mental Model Shift

Excel

DAX

Automatic Filters

Filter Context

Row Context

Automatic Filters

Primary Sources of Automatic Filters

Click to Explore

Filter Context

CALCULATE

Common CALCULATE Patterns

Row Context

The Row Context Problem

Context Transition

Iterator Functions

Key Takeaways

DAX Is Context-Driven

Filter Context Narrows Data

Row Context Iterates

CALCULATE Modifies Filter Context

Context Transition Bridges the Gap

X Functions Evaluate Per Row

Understanding DAX

DAX Patterns: Time, Variables, and Beyond