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

Part 2 of 3 — DAX Fundamentals
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( Sales[Amount] )
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.

Filter Context

The set of active filters that narrow which rows a measure can see.

Most Common

Row Context

Created by iterators and calculated columns -- points at one row at a time.

Calculated Columns

Context Transition

CALCULATE converts row context into filter context -- the bridge between the two.

Advanced

Query Context

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 query context is the combination of all filters and groupings that shape which data the measure evaluates against. Click each element below to see how it contributes.

Power BI Report
Year ▾
2022
2023
2024
Table Visual
ProductSales
Mountain Bike$540K
Road Bike$660K
Helmet$30K
Sales by Category
Bikes
Acc.
Cloth.
Filters
Category = Bikes Year >= 2022

Click to Explore

Click any element in the report above, or any source chip below, to see how they connect.

Seven Sources of Query Context

Rows & Columns Visual Filters Slicers Page Filters Report Filters Cross-Highlighting CALCULATE

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. Filters stack -- each layer narrows the data further. Step through the filters below to see the funnel in action.

All Data: 20 rows — $5,082,000
Step 0 of 3

Key point: Filters are additive. Each new filter can only narrow the data further -- it can never expand it (unless you use CALCULATE to override an existing filter). This is the "funnel" nature of filter context.

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.

Add Filters

Total Sales =
CALCULATE (
  SUM ( 'Sales'[Amount] )
)
Result
$5,082,000
No filters applied. CALCULATE evaluates SUM('Sales'[Amount]) against all 20 rows, returning the grand 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.

The Row Context Problem

// Calculated column on Products table
Product Sales = SUM ( 'Sales'[Amount] )
ProductCategoryProduct Sales
Without CALCULATE, row context exists but SUM ignores it -- it sums the entire column. Every row gets the grand total of $5,082,000.

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

Context Transition

When CALCULATE converts row context into filter context

Context transition is what happens when CALCULATE runs inside a row context. It takes the current row's column values and converts them into an equivalent filter context. This is the bridge between row context and filter context.


Row Context
Product = "Mountain Bike"
Just reading this row's value
➔

CALCULATE
Triggers transition
Converts row values into model-wide filters
➔

Filter Context
Product = "Mountain Bike"
Now the entire model is filtered to Mountain Bike

Same value, different mechanism. Row context means you're reading one row's value. Filter context means the entire model is filtered -- every measure now sees only Mountain Bike data. CALCULATE bridges this gap.

This is exactly what fixes the row context problem from the previous section. Wrapping a measure in CALCULATE inside a calculated column converts each row's values into a filter:

// Without context transition -- returns grand total every row
Product Sales = SUM ( 'Sales'[Amount] )

// With context transition -- returns per-product total
Product Sales = CALCULATE ( SUM ( 'Sales'[Amount] ) )

When does context transition happen?

• When CALCULATE runs inside an iterator (SUMX, AVERAGEX, etc.)
• When a measure reference appears in a calculated column (measures have implicit CALCULATE)
• Every column of the current row becomes a filter -- this can be expensive on wide tables

Iterator Functions

The "X" functions that combine row context with aggregation

Iterator functions (SUMX, AVERAGEX, MAXX, etc.) loop through a table row by row, evaluate an expression for each row, then aggregate the results. They create row context internally, giving you fine-grained control.

SUMX ( 'Sales', 'Sales'[Qty] * 'Sales'[Amount] )

Iterates each row in Sales, multiplies Qty by Amount, then sums all results. Useful for row-level calculations before aggregation.

AVERAGEX ( 'Products', CALCULATE ( SUM ( 'Sales'[Amount] ) ) )

Iterates each product, calculates total sales per product (via context transition), then averages. Result: $847,000 per product.

MAXX ( 'Products', CALCULATE ( SUM ( 'Sales'[Amount] ) ) )

Finds the product with the highest total sales. Returns $2,580,000 (Road Bike).

COUNTX ( 'Products',
  IF ( CALCULATE ( SUM ( 'Sales'[Amount] ) ) > 100000, 1 )
)

Counts products whose total sales exceed $100K. Uses context transition inside the iterator.

AVERAGE vs AVERAGEX

The classic gotcha. Same data, very different results. Notice how Mountain Bike and Road Bike appear multiple times -- AVERAGE counts every row, while AVERAGEX groups by product first.

AVERAGE (Row-Level)

Avg Sales =
AVERAGE ( 'Sales'[Amount] )
#ProductAmount

AVERAGEX (Product-Level)

Avg per Product =
AVERAGEX (
  'Products',
  CALCULATE ( SUM ( 'Sales'[Amount] ) )
)
#ProductTotal Sales

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. Every slicer, filter pane, and visual grouping adds a filter layer. Filters are additive -- they only shrink the data set.
  • Row context iterates. Calculated columns and X-functions create row context, pointing at one row at a time. But aggregations like SUM ignore it.
  • CALCULATE modifies filter context. It can add, remove, or replace filters. It's 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.
  • AVERAGE vs AVERAGEX is a classic trap. AVERAGE divides by rows (20 transactions). AVERAGEX iterating Products divides by unique products (6).
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
1 / 9