Downloads

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

Row context and filter context

Row context: one row at a time A calculated column reads the current row. Channel Amount Cost Store 1,000 600 Online 500 300 Online 800 450 Store 700 420 Margin on the highlighted row: 500 - 300 = 200 Filter context: which rows SUM can see A measure reads only the rows the filters allow. Channel Amount Cost Store 1,000 600 Online 500 300 Online 800 450 Store 700 420 SUM(Amount) with Channel = Online: 500 + 800 = 1,300
Row context looks at one row at a time. Filter context decides which rows a measure can see.

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

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.

Tip

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.