Calculated Columns vs Measures
DAX lets Arjun write a formula in two places: as a calculated column, which adds a new column to a table, or as a measure, which lives in the field list and appears when dropped into a visual. Both can show a number on a card. Yet one takes memory and can sit on a slicer, while the other takes no memory and changes with every click. Choosing between them is most of everyday DAX design.
The rule
Columns compute per row at refresh; measures compute per cell when viewed.
Calculated column (Table tools → New column). The formula runs once for every row of the table, at refresh, and the results are stored in the model. Inside the formula you can name the current row's columns directly, because DAX knows which row it is on. That awareness of "this row" is called row context.
Price Band = IF ( Sales[Price] >= 30, "Premium", "Standard" )The six Sales rows get Premium, Standard, Premium, Premium, Standard, Premium (prices 30, 20, 40, 30, 20, 40). Because the values are stored, Price Band can go on a slicer, an axis or a legend.
Measure (Home → New measure). Nothing is stored. When a visual needs the value, the measure runs once for each cell, under that cell's filters — its filter context. There is no "current row", so a measure must summarise.
Total Qty = SUM ( Sales[Qty] )| City | Total Qty |
|---|---|
| Pune | 9 |
| Mumbai | 11 |
| Total | 20 |
The same measure, split by Price Band, gives Premium 13 and Standard 7.
A measure cannot use a bare column: Bad = Sales[Qty] * 2 fails with an error saying a single value for Qty cannot be determined. In a card there are six Qty values; which one should it use? Wrap the column in an aggregate instead.
If you need a column, consider building it in Power Query first. A DAX calculated column is the right tool when the column depends on other DAX results or on relationships in the model.
The shortcut
Ask "Will I slice by it, or summarise it?" Slice, filter, group or sort by it → a column. Summarise it, compare it, or divide one total by another → a measure. A ratio of totals must be a measure, because an average of per-row ratios is not the ratio of the totals.
"Let me filter by price band"
A slicer needs a stored value on every row, so Price Band is a calculated column.
Drills
Answer fast — trust the rule.
Meera wants a slicer with the options Premium and Standard. What should Price Band be?
In a table visual by City, what does Total Qty = SUM ( Sales[Qty] ) show for Pune?
What does Total Qty show for the Premium price band?
Why does the measure Bad = Sales[Qty] * 2 fail?
What does Revenue per Order show for Mumbai?
Remember
- Columns compute per row at refresh; measures compute per cell when viewed.
- Slice by it → column; summarise or divide it → measure.
- Traps: a bare column inside a measure, and storing a ratio as a column.