Day 5 — Measures and calculated columns

The choice everyone gets wrong at first, and the reason reports get slow.

Two things that look identical and are not

	Calculated column	Measure
Calculated	Once, at refresh	Every time a visual draws
Stored	Yes — takes memory	No — just a formula
Works row by row	Yes	No, unless you use an iterator
Reacts to slicers	No	Yes
Can be used as a slicer	Yes	No

The deciding question

Does the answer change when someone clicks a slicer? If yes, it must be a measure. If no — and you need to group, filter or slice by it — it can be a column.

"Total sales" changes with every slicer, so it is a measure. "Price band" is a fixed property of a product, so it can be a column.

A calculated column, conceptually

A column is computed for every row and stored. On the Sales table you could write:

Margin = Sales[Amount] - Sales[Cost]

That is legitimate but wasteful — 48 stored numbers to hold something DAX can work out on the fly. On a ten-million-row fact table it is 10 million stored numbers.

The habit to break People coming from Excel add a calculated column for everything, because in Excel that is exactly what you do — you add a column and drag it down. In Power BI it bloats the model and slows the refresh. The measure below gets the same answer and stores nothing.

The same job as a measure

No stored column, and unlike a column it responds to every slicer:

When a column really is the right answer

When you need to group or slice by the result. A measure cannot go on the axis of a chart or into a slicer — only a column can.

Price Band = IF(Product[Price] >= 10000, "High", IF(Product[Price] >= 2000, "Medium", "Low"))

That belongs on the Product table as a column, because you want to put it on the rows of a visual. And notice which table it goes on — the dimension, where there are six rows, not the fact table where there are millions.

Better still: do it in Power Query A price band is fixed. Power Query can create it at refresh, and the model does not need the DAX at all. The rule from Day 2 still holds — the earlier you do a fixed calculation, the better.

Naming measures properly

You will write a hundred of these. Names are how you find them again.

Good	Bad	Why
Total Sales	Measure 1	Says what it is
Sales LY	Sales2	Reads in a visual header
Margin %	MarginPercentage	Spaces are allowed — use them
Orders	CountOfSalesID	Business language, not model language

The measure name is what the user sees It becomes the column heading in every visual. Name it as you would want it to appear on a slide, because that is where it will end up.

Measures referring to measures

Build small measures and stack them. Define Total Sales once, then use it everywhere:

In real Power BI you would write Margin % = DIVIDE([Margin], [Sales]), referring to the other measures by name in square brackets. Change the definition of Sales once and everything built on it follows. This playground needs each measure written out in full, but the principle is the one that matters.

Try these yourself

Decide whether "number of orders this month" is a measure or a column.
Decide whether "product category" is a measure or a column.
Write a measure for average order value.
Explain why a price band column belongs on Product rather than Sales.
Rename the measure "CountOfSalesID" to something a manager would understand.

← PreviousDay 4 — The data model and relationships Next →Day 6 — The measures you will write every day