Jasper Alblas
DAX Essentials: A Beginner’s Guide to the Language Behind Your Data Models
If you’ve ever opened Power BI or worked with an SSAS Tabular model and wondered what those formulas in the measures pane actually do — this article is for you. DAX (Data Analysis Expressions) is the formula language that powers calculations across Microsoft’s analytical stack, and once you understand its core mechanics, you’ll find that even complex business logic becomes surprisingly approachable.
This guide covers three of the most important concepts you need to become productive in DAX: filter context and CALCULATE, iterators like SUMX and AVERAGEX, and variables with VAR / RETURN. By the end, you’ll have a solid mental model to build from.
What Is DAX, and Why Does It Matter?
DAX is a functional language designed specifically for working with tabular data models. You write DAX measures — named calculations that are evaluated dynamically depending on the filters applied in a report or query. Unlike a SQL query that runs once and returns a fixed result, a DAX measure recalculates every time the filter context changes.
That’s the key distinction, and it’s worth sitting with for a moment: DAX always evaluates inside a context. A measure for Total Sales doesn’t just return one number — it returns the right number for wherever it’s being evaluated: this month, this product, this region.
Filter Context and CALCULATE
What is filter context?
Every DAX expression is evaluated inside a filter context — a set of active filters that determine which rows in your tables are visible to the calculation. These filters come from slicers, report page filters, row/column headers in a matrix, and relationships between tables.
Consider a simple measure:
Total Sales = SUM(Sales[Amount])
This sums every value in Sales[Amount] that is currently visible given the filter context. Drop it into a matrix with months on rows and product categories on columns, and DAX automatically evaluates it once per cell — each time in a different filter context.
CALCULATE: the most important function in DAX
CALCULATE is the function that lets you modify the filter context before evaluating an expression. Its signature is:
CALCULATE(<expression>, <filter1>, <filter2>, ...)
Each additional argument adds, replaces, or removes filters from the context before the expression is evaluated. Here’s a simple example:
Sales Online = CALCULATE(SUM(Sales[Amount]), Sales[Channel] = "Online")
This measure always filters to the Online channel, regardless of what the report’s other filters say. It overrides the channel filter for that specific calculation.
A slightly more advanced pattern uses ALL to remove filters entirely:
Sales All Products =
CALCULATE(
SUM(Sales[Amount]),
ALL(Product)
)This returns total sales across all products, even when the report is filtered to a specific product. It’s the foundation of percentage-of-total calculations:
% of Total Sales =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALL(Product))
)Understanding that CALCULATE transitions filter context is the single biggest unlock in DAX. Almost every advanced measure is built on top of this concept.
Iterators: SUMX, AVERAGEX, and Friends
Why iterators exist
Sometimes you need to perform a calculation row by row before aggregating the result. That’s exactly what iterator functions do — they walk through a table row by row, evaluate an expression for each row, and then aggregate the results.
The most common iterators follow the <aggregation>X naming pattern:
SUMX— sums a row-level expressionAVERAGEX— averages a row-level expressionMINX/MAXX— finds the min/max of a row-level expressionCOUNTX— counts rows where a row-level expression is non-blank
SUMX in practice
The classic example is calculating revenue when unit price and quantity live in separate columns:
Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
SUMX iterates over every row in the Sales table, multiplies Quantity by UnitPrice for that row, and sums all the results. You couldn’t do this with a plain SUM because SUM only works on a single column.
This pattern becomes powerful when you combine it with table functions. For example, calculating average order value across only completed orders:
Avg Completed Order Value =
AVERAGEX(
FILTER(Orders, Orders[Status] = "Completed"),
Orders[OrderTotal]
)
Here, FILTER produces a sub-table of only completed orders, and AVERAGEX averages the total across those rows.
The mental model
Think of iterators as a loop:
- For each row in the table…
- Evaluate the expression in the row context of that row
- Aggregate all the results
The phrase “row context” is important — inside an iterator, DAX knows which row it’s currently on, which means you can reference column values directly by name. This is different from filter context, though the two interact in subtle ways as you advance further in DAX.
Variables: VAR / RETURN
The problem variables solve
Before variables existed in DAX, writing complex measures meant either nesting functions deeply or repeating the same sub-expression multiple times. Both approaches are hard to read and error-prone.
Variables let you store an intermediate result and reuse it within the same measure. The syntax is:
MeasureName =
VAR SomeValue = <expression>
RETURN
<expression using SomeValue>
A practical example
Here’s the percentage-of-total pattern from earlier, rewritten with variables:
% of Total Sales =
VAR CurrentSales = SUM(Sales[Amount])
VAR AllSales = CALCULATE(SUM(Sales[Amount]), ALL(Product))
RETURN
DIVIDE(CurrentSales, AllSales)
This is much easier to read. Each variable has a clear name, and the RETURN statement reads almost like plain English: divide current sales by all sales.
Variables capture the filter context at definition time
This is a subtlety worth knowing early: a variable is evaluated when it’s defined, not when it’s used. This means the filter context at the point of the VAR line is what the variable captures. Practically speaking, this rarely trips you up, but it matters when you’re doing more complex context transitions with CALCULATE.
Good habits with variables
- Give variables descriptive names —
VAR CurrentMonthSalesis far better thanVAR X - Use variables to avoid repeating expensive sub-expressions
- Break complex measures into multiple variables so the logic reads top to bottom
Sales vs Last Year % Change =
VAR SalesThisYear = SUM(Sales[Amount])
VAR SalesLastYear = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))
VAR Difference = SalesThisYear - SalesLastYear
RETURN
DIVIDE(Difference, SalesLastYear)Clean, readable, and self-documenting.
Putting It All Together
These three concepts — filter context with CALCULATE, iterators, and variables — aren’t isolated features. They work together:
Revenue Per Active Customer =
VAR TotalRevenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
ActiveCustomers =
CALCULATE(DISTINCTCOUNT(Sales[CustomerID]),
Sales[Status] = "Active"
)
RETURN DIVIDE(TotalRevenue, ActiveCustomers)This single measure uses all three concepts: a variable to hold a SUMX-calculated revenue, another variable using CALCULATE to count only active customers, and a clean RETURN to express the final logic.
What to Learn Next
Once these fundamentals feel natural, the logical next steps are:
- Time intelligence functions —
DATEADD,SAMEPERIODLASTYEAR,TOTALYTDfor period-over-period analysis - RANKX and ranking patterns — for leaderboards and top-N analyses
- Row context vs. filter context transitions — the deeper mechanics behind
CALCULATEinside iterators - Calculation groups — a powerful SSAS Tabular feature for applying reusable calculation logic across measures
DAX has a reputation for being difficult, but most of that difficulty is concentrated in a handful of concepts — and filter context is the main one. Once you internalize how context flows through a calculation, the rest of the language opens up quickly.
If you found this useful, I write regularly about data engineering, analytics, and BI on this blog. Feel free to reach out or leave a comment below.
