Measures vs Calculated Columns
React to filters, or store a per-row fact
The single most common DAX question: measure or calculated column? The rule: if it must react to filters, it's a measure; if it's a static per-row fact, it's a column.
What you will learn
- Decide measure vs column from first principles
- Recognize the performance cost of calculated columns
- Refactor a calculated column into a measure
What is this?
Measures are evaluated per query in filter context; calculated columns are evaluated once per row at refresh and stored. A measure answers 'as-of-now, given selection'; a column answers 'what is true for this row forever'. The same math can live either place — the choice is dictated by whether the answer should change when filters change, plus the memory/refresh cost of materializing it.
Why do you need it?
- Measures stay lean: nothing is stored, everything is computed on demand.
- Calculated columns bloat the model: every value is persisted and compressed.
- Mis-using columns breaks slicer-driven answers (the report looks wrong).
- Refactoring to measures is the highest-leverage DAX performance fix.
Mental model
An ingredient's price tag printed on the jar
A calculator that re-reads today's prices
The printed tag (calculated column) is fixed at print time — restocks and sales don't change it. The calculator (measure) re-computes with the current selection and current state each time you press it. Tags cost storage; calculators cost compute.
How it works
- Calculated columns are computed when the table is loaded/refreshed, stored in the model's columnar store.
- Measures are compiled into the query plan and evaluated for the current filter context only.
- A column can reference other columns' values per row; a measure references aggregations and context.
- Measures can call calculated columns; columns cannot evaluate aggregations with user filters.
- Storage-wise, a column is persisted (space); a measure is ephemeral (compute).
Deciding measure vs calculated column.
Examples
Same math, two homes
-- Calculated column (stored per row):Net = 'fact_orders'[amount] - 'fact_orders'[bonus] -- Measure (computed per query, reacts to filters):Net Revenue = SUMX(fact_orders, fact_orders[amount] - fact_orders[bonus])The column freezes the value per row at refresh; the measure recomputes the filtered net every time the report or slicer changes.
In production
- A customer table stores a fixed segment column (static classification) — columns are fine there.
- An AOV metric is a measure: slicing by region changes the divisor, so nothing can be stored.
- A refactor moves a dozen 'Total x' columns into model measures, cutting refresh time and model size.
Best practices
- Default to measures; escalate to columns only for true row-level static facts.
- Move money/ratio math off columns into measures.
- Keep columns that feed filtering/grouping (categories, keys) but not derived aggregates.
- Document why a column exists — the next person will otherwise 'fix' it into a measure.
Common mistakes
- Putting slicer-reactive math in columns, so the dashboard never updates on selection.
- Ignoring the refresh-time and memory cost of bulk columns.
- Using a column inside a measure when the measure should aggregate a base column.
- Duplicating a well-known measure as a column for a single table view.
Debugging tips
- Number not changing with slicers? It's a column — refactor to a measure.
- Model bloating with refresh slow? Hunt for derived columns that should be measures.
- Two visuals disagree? One references a stored column; the other a measure — unify them.
Interview checkpoint
Quick quiz
A figure must rise/fall when a user changes a date slicer. It should be a:
Calculated columns are computed again on every slicer change.