Measures, Quick Measures and CALCULATE

What it is

A measure is a DAX formula that calculates a result when a visual asks for it. The result depends on the filter context: which slicers, rows and columns are active. CALCULATE is the key function, because it changes the filter context before it evaluates an expression. Quick measures generate common measures for you.

How it works

Single aggregation measures

Explicit measures are created with New measure and a name. The aggregation functions are:

Total Sales = SUM ( Sales[Amount] )
Order Count = DISTINCTCOUNT ( Sales[OrderID] )
Average Price = DIVIDE ( [Total Sales], SUM ( Sales[Quantity] ) )

Prefer DIVIDE to the / operator, because it handles division by zero. An implicit measure is a column dragged into a visual with an aggregation. An explicit measure is one you write. Explicit measures are reusable, can be formatted once and are required by calculation groups.

Iterator functions

Functions ending in X, such as SUMX and AVERAGEX, evaluate an expression row by row and then aggregate. Use them when the value needs row level math.

Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

A simple SUM cannot multiply two columns row by row.

Row context and filter context

• Row context exists when DAX visits one row at a time, as in a calculated column or inside an iterator.
• Filter context is the set of filters that apply to a calculation. A matrix cell showing Bikes in 2024 has a filter context of Category = Bikes and Year = 2024.

A measure in a matrix is evaluated once for every cell, each with its own filter context.

CALCULATE

CALCULATE evaluates an expression in a modified filter context.

Red Sales = CALCULATE ( [Total Sales], Product[Color] = "Red" )
Sales All Products = CALCULATE ( [Total Sales], ALL ( Product ) )
Share of Total = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Product ) ) )

Key points:

• A Boolean filter such as Product[Color] = "Red" replaces any existing filter on that column.
• ALL removes filters from a table or columns. REMOVEFILTERS is a clearer name for the same use inside CALCULATE.
• ALLSELECTED removes filters but keeps those from slicers and outer visuals, which is useful for a percentage of the visible total.
• KEEPFILTERS adds a filter instead of replacing the existing one.
• FILTER returns a table of rows that meet a condition, for example when the condition compares two measures. A simple Boolean filter is faster.

Variables

VAR stores a value once, which makes formulas easier to read and often faster.

Sales vs Target % =
VAR Actual = [Total Sales]
VAR Target = [Sales Target]
RETURN DIVIDE ( Actual - Target, Target )

Quick measures

A quick measure is created from New quick measure and chosen from a list. You select the calculation, drag fields to the boxes, and Power BI writes the DAX. You can read and edit it afterwards. The categories include aggregate per category, filters, time intelligence, totals (such as running total), mathematical operations and text. Examples are Average per category, Running total, Year over year change and Percent difference from filtered value. Quick measures are a good way to learn DAX patterns, and the result is an ordinary explicit measure.

A worked scenario

A manager wants a card showing the share of sales that comes from the selected category compared with all categories. The analyst writes DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Product[Category] ) ) ). When the card is placed in a visual filtered by a category slicer, the numerator is that category and the denominator ignores the filter. She uses DIVIDE so the card shows blank, not an error, when nothing is selected.