DAX: measures, calculated columns and context

In this lesson you will learn

Choose when to use a calculated column and when a measure
Explain row context and filter context
Write the basic aggregation measures and predict their results
Write readable, safe formulas with VAR/RETURN and DIVIDE

Elvin has to show his manager three numbers: total sales, average order value and number of customers — for the whole company and separately for every city and month. In Excel that would take dozens of formulas. In Power BI a measure written once in DAX (Data Analysis Expressions) recalculates itself for whatever filter is selected in a visual.

#	City	Product	CustomerID	Quantity	UnitPrice	Amount
1	Baku	Headphones	C1	2	60	120
2	Ganja	Notebook	C2	10	5	50
3	Baku	Keyboard	C1	1	45	45
4	Sumgait	Backpack	C3	3	40	120
5	Baku	Notebook	C4	20	5	100
6	Ganja	Headphones	C1	1	60	60
7	Baku	Backpack	C1	2	40	80
8	Sumgait	Keyboard	C3	2	45	90

The Sales table (January–March 2026). Customers: C1 — Aysel, C2 — Murad, C3 — Leyla, C4 — Elvin. Amount is a calculated column.

Calculated column versus measure

Calculated column: per row, computed at refresh and stored

// Table tools › New column
Amount = Sales[Quantity] * Sales[UnitPrice]

Measure: computed on the fly for the visual's filters, not stored

// Home › New measure
Total Sales = SUM ( Sales[Amount] )

Both formulas look at the same table but run at different times. The Amount column is computed once for each of the 8 rows at refresh and stored in the model. The Total Sales measure is stored nowhere: it is recalculated when the visual opens, when a slicer changes or when you select something in another visual. In the Data pane measures have a calculator icon, while calculated columns look like ordinary columns.

Feature	Calculated column	Measure
When it is computed	once, at refresh	every time a visual is drawn
Memory	takes space in the model	takes no space
Context	row context	filter context
Where you use it	slicers, axes, row/column headers	`Values`: numbers, percentages, KPIs

Rule of thumb: if you need to filter or group by the result, use a column; if you need to aggregate and compare, use a measure.

Row context and filter context

Definition

Row context

The formula's knowledge of “which row am I on”. In a calculated column Power BI walks the table row by row: on row 1 Sales[Quantity] is 2 and Sales[UnitPrice] is 60, so Amount = 120.

Definition

Filter context

The set of all filters placed on the model before a measure is computed: the visual's row and column headers, slicers, the Filters pane and cross-highlighting. On the “Baku” row Total Sales sees only the 4 Baku rows: 120 + 45 + 100 + 80 = 345.

Basic aggregation measures

DAX

Total Sales = SUM ( Sales[Amount] )
Orders = COUNTROWS ( Sales )
Avg Order = AVERAGE ( Sales[Amount] )
Customers = DISTINCTCOUNT ( Sales[CustomerID] )
Units = SUM ( Sales[Quantity] )

Each line is a separate measure (Home › New measure). COUNTROWS counts the rows of a table, DISTINCTCOUNT counts the different values in a column.

City	Total Sales	Orders	Avg Order	Customers	Units
Baku	345	4	86.25	2	25
Ganja	110	2	55	2	11
Sumgait	210	2	105	1	5
Total	665	8	83.125	4	41

A matrix visual by Store[City]. Note: in Customers 2 + 2 + 1 = 5, yet the total is 4 — Aysel (C1) bought in both Baku and Ganja, and the total row counts her once.

Variables (VAR/RETURN) and DIVIDE

DAX

Sales per Customer =
VAR SalesAmt = [Total Sales]
VAR Cust = [Customers]
RETURN
    DIVIDE ( SalesAmt, Cust )

VAR gives an intermediate result a name and RETURN returns the final expression. A variable is evaluated once and the formula becomes readable.

City	Total Sales	Customers	Sales per Customer
Baku	345	2	172.5
Ganja	110	2	55
Sumgait	210	1	210
Total	665	4	166.25

345 / 2 = 172.5; 110 / 2 = 55; 210 / 1 = 210; in the total 665 / 4 = 166.25 ₼.

Why DIVIDE instead of /? When the denominator is 0 or blank (BLANK), / can return an error or infinity, while DIVIDE returns a blank and the visual stays clean. A third argument gives an alternative result: DIVIDE ( [Total Sales], [Customers], 0 ). For example, a new branch with no sales shows a blank Sales per Customer instead of an error.

Worked example: the Electronics category

The slicer has Category = “Electronics” selected. What will the Total Sales, Orders, Avg Order, Customers and Sales per Customer measures show? Work it out on paper first, then check with a visual.

Show solution

1) Filter context: the Headphones and Keyboard rows — rows 1, 3, 6 and 8.
2) Total Sales = 120 + 45 + 60 + 90 = 315 ₼; Orders = 4.
3) Avg Order = 315 / 4 = 78.75 ₼.
4) Customers: C1, C1, C1, C3 → distinct values C1 and C3 = 2.
5) Sales per Customer = DIVIDE(315, 2) = 157.5 ₼.
We did not change any measure — only the filter context changed.

New line below in the DAX formula barShift+Enter

Commit the formulaCtrl+Enter

Show suggestions (IntelliSense)Ctrl+Space

Comment or uncomment a lineCtrl+/

Expand or collapse the DAX formula barCtrl+J

Key points

A calculated column works in row context and is stored; a measure is computed on the fly in filter context.
Filter context comes from the visual's headers, slicers, the filter pane and cross-highlighting.
DISTINCTCOUNT and AVERAGE are not additive: the total row is recalculated.
Write explicit measures instead of relying on implicit ones.
VAR/RETURN simplifies formulas; DIVIDE returns a blank on division by zero.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

You want to group products into “Cheap / Expensive” and show that in a slicer. What should you create?