Calculated column or measure?

The idea

DAX does two different jobs, and they look almost the same on the screen. You type Name = expression, press Enter, and a new thing appears in the Data pane. But the engine treats the two in opposite ways.

• A calculated column is evaluated once per row, when the data is loaded or refreshed. The result is stored in the table and takes up memory like any other column.
• A measure is evaluated once per cell, when a visual is drawn, under the filters that cell carries. Nothing is stored; it is a formula waiting to be asked.

One rule picks between them: if you need to group, filter or slice by it, it must be a column. If you need to add up or average it, it must be a measure.

Why it exists

A spreadsheet has only one kind of formula: it lives in a cell and shows a value. Power BI tables have thousands or millions of rows, and its questions are of two different kinds.

Some questions are about a row: "is this product premium?", "what month was this order?", "what did this line cost?". They have one answer per row and the answer does not change when you look at the row from a different chart. That is a column.

Other questions are about a set of rows: "what is our average order value for the web channel?". The answer depends on which rows the visual, the slicers and the row header hand over. You can't store it, because it is not one number. That is a measure.

Mixing them up has predictable costs. A ratio built as a column returns one value per row that the matrix can only add or average, which is wrong. A band built as a measure cannot go on a row or a slicer, because a measure is not a column to group by.

How it works under the hood

Row context: the current row

When a calculated column runs, DAX visits the table's rows in turn. At each stop, the row is the row context: products[unit_price] means that row's price. That is why IF, SWITCH, YEAR and MONTH work directly in a column. They need one value, and the row context supplies it.

Row context carries no filter. If a column on orders calls SUM(order_items[line_total]), it returns the grand total on every row, because the current order does not filter the lines. Making a row act as a filter takes CALCULATE, which is the next chapter's subject.

Filter context: the cell's rows

A measure starts from the cell. The row header, the column header, the slicers and the page filters become the filter context, and the measure's expression runs over the rows that survive.

Two details come out of this. First, a measure never lists rows: SUM, COUNTROWS, DISTINCTCOUNT, AVERAGE, MIN and MAX all collapse a column or table to one number. Second, the Total row is not a sum. It is the measure with the row header's filter missing, which for a ratio gives a different answer than adding the rows.

Following a relationship in a column

A column can only read its own row, but the model has related tables. RELATED reads a column from the one side for the current row: Line Cost = order_items[quantity] * RELATED(products[unit_cost]). It needs row context and follows the relationship from the many side to the one side.

Measures calling measures

Once Sales exists, you can write Average Order Value = DIVIDE([Sales], [Orders]). A measure reference is written with square brackets and no table name, which is how DAX keeps columns (table[column]) and measures apart on sight. Build small measures, then compose them. Use DIVIDE rather than / so a cell with no orders shows blank, not an error.

Where to create each

Both start in the same place. In Power BI Desktop, select a table in the Data pane and choose Table tools → New column or New measure (a measure can live in any table, because it is not a column of it). In Table view you will see a column's values; you never see a measure's.

A quick test before you type

Ask two questions. Do I need this value on a row header, a slicer or a filter? Then it is a column. Does its answer change with the cell it sits in? Then it is a measure. A price band, an order month and a line cost pass the first test. Customers who bought, price range and average order value pass the second. If both answers are no, you probably need a different measure or none at all.

Common misconceptions

"A measure is just a column that is calculated later"

They are different objects. A column is data: it has a value per row and is stored. A measure has no value of its own, only a result for a given context. The same measure can show 704.57 in one cell and 1550.61 in the next. A column cannot change like that.

"I'll make it a column so it is faster"

A column is computed at refresh and costs memory on every row, including the ones nobody looks at. A measure costs nothing until a cell asks. For anything that you aggregate, a measure is usually both smaller and correct. Reach for a column when the value is a property of the row and you need to group or filter by it.

"The Total row adds up the rows above it"

It runs the measure again, without the row header's filter. For Sales the result happens to equal the sum. For Average Order Value it does not: 1067.02 is neither the sum of 704.57 and 1550.61 nor their average, because it is the average over all seven orders.