DAX fundamentals
Measures versus columns, row and filter context, VAR and RETURN, and your first measures.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Tell measures, calculated columns and calculated tables apart.
- Explain row context and filter context, and how each changes a result.
- Write measures with VAR and RETURN, and handle divide-by-zero with DIVIDE.
- Build a first set of sales measures on the sample model.
Data Analysis Expressions (DAX) is the formula language of Power BI, Power Pivot in Excel and Analysis Services tabular models. It is a functional language: every formula is an expression that returns a value or a table. Most DAX confusion comes down to one idea, evaluation context, so learn that first.
What DAX is for: measures, calculated columns, calculated tables
- Measure: a formula evaluated at query time, in the filter context of wherever it is placed (a card, a matrix cell, a chart bar). It stores no data, so it is the right default for anything you aggregate:
Total Sales = SUM(Sales[SalesAmount]). - Calculated column: evaluated row by row when the model refreshes and stored in the table, using row context. Use it when the result is a property of the row you will filter, group or slice by (for example
Sales[Margin] = Sales[SalesAmount] - Sales[TotalCost]or a Customer age band). It costs memory, so do not use a calculated column for something a measure can do. - Calculated table: a table produced by a DAX table expression, computed at refresh (
Calendar = CALENDAR(DATE(2016,1,1), DATE(2018,12,31))). Available in Power BI Desktop and Analysis Services; Power Pivot in Excel has no New Table command (see the Power Pivot topic for workarounds). - Rule of thumb: aggregate with a measure; classify or filter by with a column; build lookup/helper tables as tables.
Row context and filter context
- Filter context is the set of filters currently applied to the model: slicers, rows and columns of a matrix, visual and page filters, and filters added by CALCULATE. Aggregators such as SUM, COUNTROWS and DISTINCTCOUNT only see the rows that survive the filter context. This is why one measure shows a different number in every cell.
- Row context means "the current row of one table". Calculated columns have it automatically; iterator functions (SUMX, AVERAGEX, FILTER, ADDCOLUMNS ...) create it while they loop. Inside row context you can refer to a column of the current row, as in
Sales[Quantity] * Sales[UnitPrice]. - Row context does NOT filter other tables, and by itself it does not filter the same table's aggregations. To turn a row context into a filter, wrap the expression in CALCULATE (this conversion is called context transition). That is why a measure inside an iterator, such as
SUMX(Customer, [Total Sales]), returns each customer's sales. - Filters flow along relationships from the one side to the many side (dimension to fact). A slicer on Product therefore filters Sales, but a filter on Sales does not filter Product unless the relationship is bidirectional.
Implicit versus explicit measures
Dragging a numeric column into a visual makes an implicit measure (a Sum of that column). Prefer explicit measures: they are reusable in other measures, can be formatted once, and show up in the DAX you can read. Hide the raw numeric columns and set discourageImplicitMeasures on the model if you want to force it.
Syntax essentials
- Column reference:
Table[Column]. Quote table names that contain spaces or special characters:'Order Details'[Quantity]. Measure reference:[Measure Name]with no table. Always qualify columns with their table and never qualify measures, so you can tell them apart at a glance. - Operators:
+ - * /,^, comparison= <> < > <= >=, text concatenation&, logical&&(and)||(or)NOT(),IN {"A","B"}andIN VALUES(Table[Col]). - Text is in double quotes, a double quote inside text is doubled
"". Dates areDATE(2018,12,31)or the typed literalDATE; do not compare dates with text. Boolean values are TRUE() and FALSE(). - Comments:
// line,-- line,/* block */. Indentation and line breaks are free; the DAX formatter (daxformatter.com) or Format Query in DAX query view makes them readable. BLANK()is not zero and not an empty string. A BLANK in a measure makes that row disappear from many visuals.COALESCE(x, 0)turns blank into 0 when you want a number to show.
Variables: VAR and RETURN
Margin % =
VAR Sales = [Total Sales]
VAR Cost = [Total Cost]
RETURN
DIVIDE(Sales - Cost, Sales)
Variables are evaluated once, in the context where they are defined (not where they are used), can hold a scalar or a table, and make formulas faster and easier to read. A variable cannot be changed after it is set, and it is evaluated before CALCULATE changes the context, a frequent source of surprise (and the reason the prior-year pattern below works).
DIVIDE, BLANK and error handling
Use DIVIDE(numerator, denominator, [alternate]) instead of /: it returns BLANK (or the alternate) when the denominator is zero or blank.
Use IFERROR sparingly because it hides real mistakes and is slower; prefer testing the condition (IF(ISBLANK(x), ...), DIVIDE).
ISBLANK, ISEMPTY, HASONEVALUE, SELECTEDVALUE(Column, [alternate]) are the main tests for what the context holds.
Data types and formatting
Whole number, Decimal (floating point), Fixed decimal (currency, 4 decimals), Text, Date/time, True/False. Floating-point math can leave tiny remainders; wrap comparisons in ROUND when equality matters. Format with the Format string of the measure (Measure tools or the model's formatString), not with the FORMAT function, because FORMAT returns text and breaks sorting and charts. Use FORMAT only for labels such as dynamic titles.
A first set of measures for a sales model
Total Sales = SUM(Sales[SalesAmount]) Total Cost = SUM(Sales[TotalCost]) Gross Margin = [Total Sales] - [Total Cost] Gross Margin % = DIVIDE([Gross Margin], [Total Sales]) Units Sold = SUM(Sales[SalesQuantity]) Order Lines = COUNTROWS(Sales) Customers = DISTINCTCOUNT(Sales[CustomerKey]) Average Price = DIVIDE([Total Sales], [Units Sold])
Build every later measure on top of these so a business rule lives in one place.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.