Module 4: DAX basics · Lesson 4.5 · about 15 min
Variables, IF and SWITCH
VAR names a step so long measures read like a recipe and run faster. IF and SWITCH choose between results. Rewrite a measure with variables and build a SWITCH(TRUE()) band.
Module 4 · about 15 minutes · read first: 4.2. You’ll be able to use VAR, IF and SWITCH to write readable measures.
1. Why this matters
Real measures grow: “growth vs last year, but blank if the store didn’t exist last year”. Variables keep them readable, and readable DAX is what reviewers and interviewers look for.
2. Variables
Measure =
VAR Name1 = <expression>
VAR Name2 = <expression using Name1>
RETURN <result using the names>
Rewrite Margin % as one measure with variables: VAR SalesAmt = ..., VAR Cost = ..., then RETURN DIVIDE(SalesAmt - Cost, SalesAmt).
Press Run to see the result.
Margin % =
VAR SalesAmt = SUM(Sales[Amount])
VAR Cost = SUMX(Sales, Sales[Quantity] * Sales[UnitCost])
RETURN DIVIDE(SalesAmt - Cost, SalesAmt) Benefits: each step has a name (self-documenting), each is computed once (faster), and you can debug by temporarily writing RETURN Cost to see a step’s value.
3. IF and SWITCH
IF(test, then, else): if else is left out, it returns blank.
SWITCH(value, match1, result1, match2, result2, …, else) compares one value to a list. With TRUE() as the value, it becomes “first test that is true wins”, which is the clean way to write bands:
Write Size that says "Big" when Total Sales is at least 100,000, "Medium" from 50,000, and "Small" otherwise, with SWITCH(TRUE(), ...).
Press Run to see the result.
SWITCH(TRUE(), test1, result1, test2, result2, else). The first true test wins, so put the biggest threshold first.
Total Sales = SUM(Sales[Amount])
Size = SWITCH(TRUE(),
[Total Sales] >= 100000, "Big",
[Total Sales] >= 50000, "Medium",
"Small"
) 4. Blank or zero?
Make Sales or zero show 0 instead of blank, using IF and ISBLANK. Split by 'Date'[YearMonth] to see Forks Market's months before it opened (a slicer keeps only Forks Market).
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Sales or zero = IF(ISBLANK([Total Sales]), 0, [Total Sales]) With blanks, Forks Market’s months before it opened disappear from the visual. With zeros, they appear as 0. Choose on purpose: zeros are right for “we sold nothing” on a day the store was open; blanks are right for “this didn’t exist”.
Principle
Principle: a VAR is evaluated once, in the filter context where it is defined. That makes measures faster and predictable, and it has one consequence to remember: if you define VAR S = [Total Sales] and later use CALCULATE(S, ...), the CALCULATE cannot change S, which was already computed. Put measures, not variables, inside CALCULATE when the filter should apply.
Try it: what does VAR X = 5 RETURN X * X give? (25.)
5. Worked example
Growth that respects new stores.
Growth % =
VAR ThisYear = [Total Sales]
VAR LastYear = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN IF(ISBLANK(LastYear), BLANK(), DIVIDE(ThisYear - LastYear, LastYear))
Forks Market opened in 2025, so its growth is blank instead of a misleading figure. You’ll build this properly in Lesson 6.2.
6. Practice
- Rewrite
Average Linewith two variables. - Write
Seasonfor'Date'[MonthNo]with SWITCH: 12, 1, 2 → “Winter”; 6, 7, 8 → “Summer”; else “Spring/Autumn”. (Hint: SWITCH(TRUE(), [M] IN {12, 1, 2}, …) withVAR M = SELECTEDVALUE('Date'[MonthNo]).) - Why might
[Total Sales] + 0be a bad idea in a table of products by store?
Answers
VAR S = SUM(Sales[Amount]) VAR L = COUNTROWS(Sales) RETURN DIVIDE(S, L).VAR M = SELECTEDVALUE('Date'[MonthNo]) RETURN SWITCH(TRUE(), M IN {12, 1, 2}, "Winter", M IN {6, 7, 8}, "Summer", "Spring/Autumn").- Every combination shows, including products a store never stocked, so the table becomes long and noisy.
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.