Variables, BLANKs and readable DAX

01The problem

Kolanut's revenue for January to June 2026 is ₦290.7m, up 19.1% on the same months of 2025. The managing director's question is the obvious one: "Are we selling more, or just charging more?" Prices went up in January 2026, so some of that growth is price and some is volume.

The measure that answers it needs several steps: revenue now, revenue at last year's prices, last year's revenue, and the differences between them. Written as one long nested formula, it's unreadable and impossible to check. Written with variables, it reads like the explanation you'd give the director.

02The concept

VAR and RETURN

Measure name =
VAR FirstStep = …
VAR SecondStep = … FirstStep …
RETURN
    SecondStep - FirstStep

Variables make measures:

• Readable: each step has a name.
• Faster: a variable is calculated once, however many times you use it.
• Debuggable: to check a step, temporarily RETURN that variable instead of the result.

One rule catches everyone: a variable is calculated where it's defined, in that filter context, and then never changes. VAR Total = [Revenue] followed by CALCULATE ( Total, … ) doesn't recalculate Total with the new filters; it returns the same number. Put the CALCULATE inside the variable's definition instead.

BLANK is not zero

DAX uses BLANK() for "no value". Visuals hide rows where every measure is blank, which is usually what you want: a product nobody bought in Kano doesn't clutter the table.

• DIVIDE ( a, b ) returns BLANK when b is 0 or blank, or a third argument if you give one: DIVIDE ( a, b, 0 ).
• [Revenue] + 0 turns blanks into zeros, and suddenly the table shows every customer for every month, thousands of empty rows. Only do it when a zero genuinely means something (a sales rep's month with no sales on a performance page).
• COALESCE ( [Revenue], 0 ) does the same job more explicitly.

Readable DAX

• One function argument per line, indented, as in the examples in this course.
• Paste long measures into DAX Formatter (daxformatter.com, a free tool from SQLBI) to lay them out consistently.
• Comments: -- or // for a line, /* … */ for a block.

03Example

First, a calculated column on products holding each product's 2025 price (context transition again: each product row filters orders):

Price 2025 = CALCULATE ( MAX ( orders[unit_price] ), 'Date'[Year] = 2025 )

Then three measures:

Revenue at 2025 Prices =
SUMX (
    orders,
    orders[quantity] * RELATED ( products[Price 2025] ) * ( 1 - orders[discount_pct] / 100 )
)

Price Effect =
VAR ActualRevenue = [Revenue]
VAR AtOldPrices = [Revenue at 2025 Prices]
RETURN
    ActualRevenue - AtOldPrices

Volume Effect =
-- growth in revenue at constant (2025) prices, against the same period last year
VAR AtOldPrices = [Revenue at 2025 Prices]
VAR LastYear = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    AtOldPrices - LastYear

For January to June 2026, in a visual filtered to those months:

The two effects add up exactly to the growth, which is how you know the logic is sound. About half of the ₦46.6m growth came from the price rise and half from selling more. That's a much better answer than "up 19%".

04Walkthrough

1. Add the Price 2025 column to products. In Data view, check that Malt drink 330ml (24) shows ₦13,200, against a list price of ₦14,800.
2. Add the three measures. Put them in a table by Date[Year] with [Revenue], and filter the visual to 2026.
3. Debug a step: change Price Effect to RETURN AtOldPrices and check it shows ₦266,071,515 for 2026. Then put the real RETURN back.
4. Try the variable trap. Write Test = VAR R = [Revenue] RETURN CALCULATE ( R, products[category] = "Snacks" ) and put it in a table by category: every row shows its own revenue, not Snacks, because R was already calculated. Delete it.
5. Put customers[customer_name] and Date[Year Month] in a matrix with [Revenue]. Then try [Revenue] + 0 and see the empty cells fill with zeros. Change it back.

05Practice

Revenue Growth % = DIVIDE([Revenue] - CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date])), CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date])))

06More practice

07Check your understanding