DAX measuresthat add up.

A measure is a formula that Power BI works out at the moment a visual asks for it, for exactly the slice of data that visual shows. That one idea explains most of DAX, and most of the numbers that come out wrong.

#Column or measure?

Use a measure for anything you add up, average or count, and a calculated column for the things you slice by. A column is worked out once per row and stored, so it costs memory on every row. A measure is worked out only when a visual shows it, for the rows in view.

A calculated column is worked out once per row, when the model refreshes, and stored:

Line Total = Sales[Quantity] * Sales[Price]

A measure is worked out when it is used, over whatever rows are in view:

Total Sales = SUMX ( Sales, Sales[Quantity] * Sales[Price] )

Both give the same total in a card. The column costs memory on every row of the table, forever; the measure costs nothing until it is shown. Keep columns for things you slice by, like a price band or a customer group.

#The first measures

On the Sales table, select New measure and add these one at a time:

Total Sales = SUM ( Sales[Amount] )

Orders = DISTINCTCOUNT ( Sales[OrderID] )

Average Order = DIVIDE ( [Total Sales], [Orders] )

DIVIDE returns blank instead of an error when there are no orders, so an empty month shows nothing rather than a broken visual. And Average Order uses the other two measures by name: each number is defined once and reused.

#Filter context

Put Total Sales in a table visual with the product categories as rows. Each row shows a different number, but the formula is the same. What changes is the filter context: for each row, Power BI filters the Sales table to that category first, then works out the measure.

Everything that filters a visual adds to that context: its rows and columns, the slicers on the page, the filters on the page and the report, and the relationships that carry a filter from one table to the next. A measure never knows which visual it is in; it only sees the rows the filter context leaves.

#Make them easy to use

• Give each measure a format (currency, whole number, percentage) once, in the measure's properties, instead of in every visual.
• Write a description. It shows when someone hovers over the measure in the field list.
• Hide the raw number columns, like Sales[Amount], so report builders use the measures and not their own sums.

CALCULATE works out an expression in a filter context you change. It is the most used function in DAX, and the one that makes shares, comparisons and fixed totals possible.

#Add or replace a filter

Red Sales = CALCULATE ( [Total Sales], 'Product'[Color] = "Red" )

In a table with colours as rows, every row of Red Sales shows the same number: the red total. The filter on 'Product'[Color] inside CALCULATE replaces any filter on that column from the visual or a slicer. Filters on other columns, like a year slicer, still apply.

When you want the visual's filter to stay and red to be added on top, wrap it in KEEPFILTERS:

Red Sales (kept) = CALCULATE ( [Total Sales], KEEPFILTERS ( 'Product'[Color] = "Red" ) )

Now the red row shows red sales and every other colour shows blank, because no row can be both blue and red.

#Remove a filter: the share of the total

The share of the total is the measure people ask for most, and the one most often built wrong. It needs the total with the product filters taken away:

All Products Sales = CALCULATE ( [Total Sales], REMOVEFILTERS ( 'Product' ) )

Share of Sales = DIVIDE ( [Total Sales], [All Products Sales] )

Format Share of Sales as a percentage. In a table of categories, the rows add up to 100%. Select a year in a slicer and they still add up to 100% of that year, because only the filters on 'Product' were removed.

#A measure inside a row

When a measure is used inside an iterator, like FILTER over a list of customers, it is worked out once per row, for that row only:

Big Customers =
COUNTROWS (
    FILTER ( VALUES ( Customer[CustomerID] ), [Total Sales] > 1000 )
)

[Total Sales] here is each customer's own total. Every measure carries a hidden CALCULATE, and inside a row it turns the row into a filter. That is called context transition, and it is why this works without writing the filter yourself.

Year to date, last year and the change are on almost every report. The functions that work them out need one thing first: a proper date table.

#The date table

Select New table and enter:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year Month", FORMAT ( [Date], "yyyy-MM" )
)

It has one row for every day of whole years, with no gaps, which the time functions rely on. Change the two years so the table covers all your orders.

Then:

1. Select the table and Mark as date table, on the Date column.
2. Sort the Month column by Month Number, so January comes before April.
3. Relate 'Date'[Date] to Sales[OrderDate], one to many. The order date must be a date, without a time part.
4. In Options, under Data load, turn off Auto date/time. With your own date table, the hidden tables it makes for every date column only take up space.

From now on, slice by the columns of 'Date', never by the date in the Sales table.

#The time measures

Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )

Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Sales YoY % = DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )

Put them in a table with 'Date'[Year Month] as rows. Sales YTD grows month by month and starts again each January. Sales PY shows the same month a year earlier, and Sales YoY % the change, blank where there was nothing last year to compare with.

#Why they work

Each of them is CALCULATE with a filter on the date table, made for you. SAMEPERIODLASTYEAR takes the dates in the current filter context and moves them back one year; TOTALYTD takes them from the first of January to the last date in view. That is why the date table must be complete, and why the slicers must use it.

#What you built

Totals and averages, a share that always adds up, and year to date, last year and the change, all on measures that give the right number in every visual.