DAX measures, calculated columns and CALCULATE

DAX questions in screenings usually test one idea: filter context. Once you understand which filters are active when a value is calculated, measures, columns and CALCULATE all make sense.

Measure or calculated column

• A calculated column is computed once per row during refresh and stored. Use it when you need a value per row to slice, group or filter by, such as an order size band.
• A measure is computed when a visual asks for it, using the filters active at that moment. Use it for totals, ratios and anything that should change with slicers.

A common mistake is creating a calculated column for profit margin and then summing it, which adds percentages. Write a measure instead:

Margin % = DIVIDE([Total Profit], [Total Sales])

DIVIDE returns blank instead of an error when sales are zero.

Filter context

Each cell in a visual has its own filters from rows, columns, slicers and page filters. Total Sales = SUM(Sales[Amount]) returns different numbers in each cell because the filters differ, not because the formula changes.

CALCULATE

CALCULATE evaluates an expression after changing the filters.

North Sales =
CALCULATE([Total Sales], Region[Name] = "North")

All Region Sales =
CALCULATE([Total Sales], ALL(Region))

The first replaces any region filter with North. The second removes region filters, which is useful as the denominator for share of total. Time intelligence works the same way: TOTALYTD([Total Sales], 'Date'[Date]) changes the date filter to year to date.

Iterators such as SUMX

SUM adds a single column. When you need a calculation per row first, use an iterator:

Revenue = SUMX(Sales, Sales[Qty] * Sales[Price])

This multiplies on each row and then adds the results. Multiplying total quantity by total price gives the wrong answer.

Habit to keep

When a number looks wrong, put the measure in a table visual with the dimension you are filtering by. Seeing the value per row usually shows whether the issue is the model, the filter or the formula.