Lesson 5 of 11 · 9 min · DAX
DAX basics: measures versus calculated columns
What you will learn
- What DAX is and where its formulas live
- Measures versus calculated columns: how and when each is calculated
- Explicit measures with
SUM,COUNTROWS,DISTINCTCOUNTandDIVIDE - Iterators such as
SUMX - Habits for tidy, reusable measures
In simple words
DAX is the formula language of Power BI, a bit like Excel formulas but made for tables. There are two ways to calculate something. A calculated column works out one value for each row, once, and stores it, like a new column in a spreadsheet. A measure is worked out on the fly, every time you look at a chart, for whatever slice you are looking at. Most of your real analysis will be measures.
Words to know
- DAX: Data Analysis Expressions, the formula language of Power BI.
- Measure: a calculation evaluated when a visual is drawn, using the filters currently applied.
- Calculated column: a new column whose value is computed for every row when the data loads.
- Iterator: a function that goes through a table row by row, such as
SUMX.
Where DAX lives
You write DAX in the formula bar when you pick New measure, New column or New table (Home or Table tools). Names of columns are written Table[Column]; measures are written [Measure Name]. Spaces and capital letters matter for readability: write Sales[Amount] and 'Date'[Date] (quote table names that contain spaces or clash with keywords).
Calculated columns
A calculated column is evaluated row by row, once, at refresh, and stored in the model (it takes memory). The formula uses the row context: it sees the current row's values.
Line Total = Sales[Quantity] * Sales[UnitPrice]
Order Size = IF ( Sales[Amount] >= 1000, "Large", "Small" )
Customer City = RELATED ( Customer[City] ) // pulls a value from the "one" side
Use a calculated column when you need a value to filter, slice or group by (a category like "Large" or "Small", an age band, a fiscal year label), because only columns can go on an axis or in a slicer. If you can create the column in Power Query instead, do: it is computed at refresh, compresses well and keeps DAX simpler.
Measures
A measure is evaluated at query time, once for each cell of a visual, using the filter context of that cell (the filters from rows, columns, slicers and page filters). Nothing is stored per row.
Total Sales = SUM ( Sales[Amount] )
Orders = COUNTROWS ( Sales )
Customers = DISTINCTCOUNT ( Sales[CustomerKey] )
Average Order Value = DIVIDE ( [Total Sales], [Orders] )
Put [Total Sales] in a chart by Month and it returns each month's sum; put it in a card and it returns the grand total; add a slicer for City and it recalculates for that city. That is the power of measures: one formula, every slice. The next lesson explains how the filter context works.
Always use DIVIDE(numerator, denominator) instead of /: it returns blank (or an alternative you give) when the denominator is zero, avoiding errors.
Margin % = DIVIDE ( [Total Sales] - [Total Cost], [Total Sales] )
Measure or column?
| Question | Measure | Calculated column |
|---|---|---|
| When is it calculated? | when a visual is drawn | at data refresh |
| Where is it stored? | nowhere (calculated on demand) | in the model, per row |
| Responds to slicers and filters? | yes | no (fixed per row) |
| Can be used on an axis, in a slicer or as a filter? | no | yes |
| Typical use | sums, ratios, averages, percentages, YTD | categories, labels, keys, flags |
A common beginner error is a column such as Margin % = Sales[Profit] / Sales[Amount] and then summing or averaging it in a chart. A ratio must be recomputed from totals, not averaged across rows. Make Margin % a measure: DIVIDE ( SUM ( Sales[Profit] ), SUM ( Sales[Amount] ) ).
Implicit and explicit measures
Dragging a numeric column into a visual creates an implicit measure (Power BI applies Sum by default). Prefer explicit measures that you write (Total Sales = SUM(...)) because they can be reused in other measures, formatted once, documented and trusted by everyone. Set a column's Summarize by to "None" for keys, years and other fields that should not be summed.
Iterators: SUMX and friends
SUM(Sales[Amount]) adds a column. When you need a calculation per row before adding, use an iterator, which has a row context for each row of the table:
Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )
Average Line = AVERAGEX ( Sales, Sales[Quantity] * Sales[UnitPrice] )
Max Order = MAXX ( VALUES ( Sales[OrderID] ), [Total Sales] )
SUMX is the measure version of "line total column, then sum", with no stored column. Other iterators: AVERAGEX, MINX, MAXX, COUNTX, RANKX. Iterators over huge tables with complex logic can be slow, so prefer plain aggregations when they are enough.
Variables
VAR and RETURN make formulas readable and faster, because each value is evaluated once:
Profit Margin % =
VAR Revenue = [Total Sales]
VAR Cost = [Total Cost]
VAR Profit = Revenue - Cost
RETURN
DIVIDE ( Profit, Revenue )
Habits for good measures
- Build up in layers: base measures (
Total Sales,Total Cost) and measures that use them (Margin,Margin %). - Name clearly, for example
Sales YTD,Sales LY,Customers (Active). - Set the format (currency, percentage, decimals) on each measure.
- Keep measures in one place: a dedicated measures table or the fact table, with display folders.
- Comment with
//or/* ... */. - Test against a known number: compare a measure to the Excel total or a SQL query for one slice.
- Use DAX query view (in recent Desktop versions) to try formulas, and Format Query or DAX Formatter to tidy them.
Common mistakes
- Creating a calculated column for something that should be a measure (a ratio, a total).
- Averaging a column of percentages instead of dividing totals.
- Dividing with
/and getting errors for zero. - Summing keys or years because Summarize by was left at its default.
- Heavy iterators over large tables where
SUMwould do.
Try it yourself
- Create
Total Sales,Orders,CustomersandAverage Order Valuemeasures and put them in cards. - Add a calculated column
Order Sizeand use it in a slicer. - Write
Margin %as a measure and as a column and compare what each shows in a chart by month. - Write
RevenuewithSUMXfor quantity times price, and compare with aLine Totalcolumn summed. - Rewrite a measure using
VARandRETURN.
Check yourself
What is the main difference between a measure and a calculated column?
A calculated column is computed per row at refresh and stored; a measure is computed on demand for the current filter context and not stored.
Why should a percentage be a measure, not a column?
Percentages must be recomputed from totals for each slice; summing or averaging a stored per-row percentage gives the wrong answer.
What does DIVIDE do that / does not?
It handles a zero or blank denominator by returning blank or a chosen alternative instead of an error.
When would you use SUMX?
When you need to compute a value for each row (such as quantity times price) and then add the results, without creating a stored column.
Interview questions
When do you use a calculated column and when a measure?
Use a column when the value must be stored per row to filter, group or slice (categories, labels, keys). Use a measure for aggregations and ratios that must respond to filters.
What are implicit and explicit measures?
Implicit measures are created automatically when a numeric column is dropped into a visual; explicit measures are written in DAX, reusable and consistent. Explicit measures are best practice.
What does VAR do in DAX?
It stores an intermediate result under a name, evaluated once, which makes formulas easier to read and often faster.
Summary
- DAX measures are evaluated per visual cell using the filter context; calculated columns are computed per row and stored.
- Use columns for grouping and slicing, measures for totals and ratios; use
DIVIDEfor safe division. SUMXand other iterators calculate per row, then aggregate;VARandRETURNtidy formulas.- Write explicit, well-named, formatted measures in layers and test them against known numbers.
Going deeper
- DAX overview: syntax, functions and context.
- Create measures in Power BI Desktop: measures step by step.
- DIVIDE function: safe division.
- SUMX function: iterator reference.
See how much of this you can explain
A free mock interview asks you new questions on Power BI and dashboards for Data Analyst and scores every answer, with what to fix.