Power BI DAX IF and SWITCH
The IF Function
The IF function checks a condition.
It returns one value when the condition is TRUE, and another value when it is FALSE.
Syntax
IF(logical_test, value_if_true [, value_if_false])
| Argument | Meaning |
|---|---|
| logical_test | A condition that is TRUE or FALSE, for example Products[UnitPrice] >= 1000. |
| value_if_true | The value to return when the condition is TRUE. |
| value_if_false | Optional. The value to return when the condition is FALSE. If you leave it out, IF returns a blank. |
It works like the IF function in Excel.
This page uses the Poke Mart model described in DAX Introduction, with the measures Total Sales from DAX Measures and Order Count from DAX Aggregation Functions.
IF in a Calculated Column
Put the products into price bands.
Add this calculated column to the Products table, as you learned in DAX Calculated Columns:
Example
Price Band = IF(Products[UnitPrice] >= 1000, "High", "Low")
For each product, IF checks if the unit price is 1,000 or more:
| ABCProduct | 123UnitPrice | ABCPrice Band |
|---|---|---|
| Poke Ball | 200 | Low |
| Great Ball | 600 | Low |
| Ultra Ball | 1,200 | High |
| Potion | 300 | Low |
| Super Potion | 700 | Low |
| Hyper Potion | 1,200 | High |
| Revive | 1,500 | High |
| Antidote | 100 | Low |
| Paralyze Heal | 200 | Low |
| Awakening | 250 | Low |
The other columns of Products are not shown.
Nested IF
Two bands are not enough.
A Great Ball (600) and a Super Potion (700) cost much more than a Potion (300).
You want three bands: High from 1,000, Medium from 500, and Low below 500.
You can put an IF inside another IF.
Select the Price Band column and change its formula to:
Example
Price Band =
IF(Products[UnitPrice] >= 1000, "High",
IF(Products[UnitPrice] >= 500, "Medium", "Low"))
This works, but each new band adds another IF and another closing parenthesis.
The formula gets harder to read.
SWITCH is a better choice.
The SWITCH Function
The SWITCH function compares an expression with a list of values.
It returns the result for the first value that matches.
Syntax
SWITCH(expression, value1, result1 [, value2, result2] ... [, else])
If no value matches, SWITCH returns the else value.
If you leave out else, it returns a blank.
Here is an example.
Kanto, Johto and Hoenn are the home regions of the first three generations of Pokemon games.
Add this calculated column to the Stores table:
Example
Generation = SWITCH(Stores[Region], "Kanto", "Gen 1", "Johto", "Gen 2", "Hoenn", "Gen 3", "Other")
| ABCStoreID | ABCStore | ABCRegion | ABCGeneration |
|---|---|---|---|
| S1 | Viridian City | Kanto | Gen 1 |
| S2 | Cerulean City | Kanto | Gen 1 |
| S3 | Goldenrod City | Johto | Gen 2 |
| S4 | Violet City | Johto | Gen 2 |
| S5 | Slateport City | Hoenn | Gen 3 |
| S6 | Lilycove City | Hoenn | Gen 3 |
"Other" is the else value.
No store gets it now, but a store in a new region would.
All the results of a SWITCH must have the same data type.
Here they are all text.
The SWITCH(TRUE(), ...) Pattern
On its own, SWITCH only checks if two values are equal.
It cannot check a range, like prices from 500 to 999.
The trick: use TRUE() as the expression, and write a condition in the place of each value.
SWITCH then returns the result of the first condition that is TRUE:
Example
Price Band =
SWITCH(
TRUE(),
Products[UnitPrice] >= 1000, "High",
Products[UnitPrice] >= 500, "Medium",
"Low"
)
Replace the nested IF formula of the Price Band column with this one.
The result is the same, and a fourth band would take just one more line:
| ABCProduct | 123UnitPrice | ABCPrice Band |
|---|---|---|
| Poke Ball | 200 | Low |
| Great Ball | 600 | Medium |
| Ultra Ball | 1,200 | High |
| Potion | 300 | Low |
| Super Potion | 700 | Medium |
| Hyper Potion | 1,200 | High |
| Revive | 1,500 | High |
| Antidote | 100 | Low |
| Paralyze Heal | 200 | Low |
| Awakening | 250 | Low |
The order of the conditions matters.
SWITCH stops at the first condition that is TRUE.
A Revive (1,500) is also 500 or more, but it gets "High", because that condition comes first.
If the line with 500 came first, no product would ever be High.
SWITCH(TRUE(), ...) works much like the IFS function in Excel.
Group by the Price Band
Price Band is a column, so you can group by it.
Add Price Band, Total Sales and Order Count to a table visual:
| Price Band | Total Sales | Order Count |
|---|---|---|
| High | 2,289,000 | 487 |
| Low | 3,904,600 | 1,542 |
| Medium | 3,223,200 | 711 |
| Total | 9,416,800 | 2,740 |
The Low products bring in the most sales.
The three High products bring in 2,289,000 from only 487 orders.
IF in a Measure
IF also works in measures.
There, the condition usually tests another measure.
This measure returns the sales of a store only if the store sold at least 1,500,000. Otherwise it returns a blank:
Example
Big Store Sales = IF([Total Sales] >= 1500000, [Total Sales], BLANK())
The BLANK() function returns a blank: an empty value.
A blank is not the same as zero.
A visual shows a blank as an empty cell.
Add Store, Total Sales and Big Store Sales to a table visual:
| Store | Total Sales | Big Store Sales |
|---|---|---|
| Cerulean City | 1,666,550 | 1,666,550 |
| Goldenrod City | 2,280,550 | 2,280,550 |
| Lilycove City | 1,373,450 | |
| Slateport City | 1,598,550 | 1,598,550 |
| Violet City | 1,083,100 | |
| Viridian City | 1,414,600 | |
| Total | 9,416,800 | 9,416,800 |
Three stores pass the test.
The other three get a blank.
Look at the Total row of Big Store Sales.
It shows 9,416,800, not 5,545,650, the sales of the three big stores.
The measure is calculated again for the Total row, where Total Sales is 9,416,800. That is more than 1,500,000, so IF returns it.
You learn why this happens in DAX Context.
Now remove Total Sales from the table, so only Store and Big Store Sales are left.
The three stores with a blank disappear: by default, a visual leaves out the rows where all the measures are blank.
The Total row still shows 9,416,800, not the sum of the three rows that are left.
Note: IF([Total Sales] >= 1500000, [Total Sales]) gives the same result, because IF returns a blank when the third argument is missing.
Writing BLANK() makes the formula easier to understand.
IF or SWITCH?
| Use | When |
|---|---|
IF | One condition with two possible results |
SWITCH | One value compared with a list of possible values |
SWITCH(TRUE(), ...) | Several conditions or ranges, checked in order |
You now know the basics of calculated columns and measures.
On the next page, you learn exactly how filter context decides what a measure calculates.