Calculated column and measure

What is a calculated column and a measure?

Calculated columns and measures are the two ways to add your own calculations to a Power BI model, and both are written in DAX. They look similar in the formula bar, which is why people mix them up, but they run at different moments and belong in different places.

A calculated column adds a new column to a table. Its value is worked out for every row when the data refreshes, and the result is stored in the model, just like any imported column. A measure is a calculation that runs at the moment a visual asks for it, in whatever filter context that visual sets, and its result is not stored anywhere.

The short version: a calculated column is a value sitting in a row; a measure is a recipe that runs on demand.

Calculated column: worked out at refresh, stored per row

A calculated column runs in row context. It walks the table row by row and can read any column on the current row, so it is the right tool when you genuinely need a value at the level of a single record.

Profit = Sales[Amount] - Sales[Cost]

This is calculated once per row during refresh and then held in the model. Because it is stored, you can use it where measures cannot go: on a slicer, in a filter, or as an axis, a legend or a grouping field in a visual. The cost is that every calculated column takes up memory and adds to refresh time, since the VertiPaq engine has to store the values the same way it stores imported data. In an Import mode model that trade-off is real, and a table full of calculated columns bloats the file.

Measure: worked out at query time, in filter context

A measure runs in filter context. It does not have a current row; instead it responds to the filters active in the visual, the slicers, the filter pane and the relationships in the model.

Total Sales = SUM ( Sales[Amount] )

Put this in a card and it sums everything. Put it in a chart by month and it recalculates for each month. The same measure gives a different answer in every cell because the filter context around it changes. Nothing is stored: the result is computed when the report is viewed and thrown away afterwards, which keeps the model lean. Measures can only be used as a value in a visual or a visual-level filter, not as an axis or a slicer.

When to use which

The rule most modellers settle on: prefer a measure, and use a calculated column only when you need the value stored at row level. A few concrete signals:

• You need to slice, filter or group by it. A product category band, a customer segment label, a fiscal week bucket: these are values you want on an axis or a slicer, so they are calculated columns.

• You are aggregating or comparing. Totals, averages, margins, year-over-year, anything that should react to the filters in the report belongs in a measure.

• The logic depends on the current row and its relationships. Looking up a value from a related table with RELATED to store on the row is calculated-column work.

There is also a third option worth naming: if a value can be produced upstream, a custom column in Power Query before the data loads is often cheaper than a calculated column, because it is computed once during load rather than living as a modelled column. Reach for a calculated column when the logic needs relationships or other tables that only exist inside the model.

What to watch out for with calculated columns and measures

• Using a column where a measure belongs. Storing a total in a calculated column freezes it at row level, so it will not respond to slicers. If the number should change as the reader filters, it has to be a measure.

• Column sprawl. Every calculated column is stored and compressed like real data, so dozens of them grow the model and slow refresh. Keep only the ones you filter or group on.

• Assuming they read context the same way. A column has row context but no filter context; a measure has filter context but no row context until an iterator gives it one. Most confusing results come from expecting one to behave like the other, so it pays to be clear on filter context and row context.