Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX IF and SWITCH


Share

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])
ArgumentMeaning
logical_testA condition that is TRUE or FALSE, for example Products[UnitPrice] >= 1000.
value_if_trueThe value to return when the condition is TRUE.
value_if_falseOptional. 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:

ABCProduct123UnitPriceABCPrice Band
Poke Ball200Low
Great Ball600Low
Ultra Ball1,200High
Potion300Low
Super Potion700Low
Hyper Potion1,200High
Revive1,500High
Antidote100Low
Paralyze Heal200Low
Awakening250Low

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")
ABCStoreIDABCStoreABCRegionABCGeneration
S1Viridian CityKantoGen 1
S2Cerulean CityKantoGen 1
S3Goldenrod CityJohtoGen 2
S4Violet CityJohtoGen 2
S5Slateport CityHoennGen 3
S6Lilycove CityHoennGen 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:

ABCProduct123UnitPriceABCPrice Band
Poke Ball200Low
Great Ball600Medium
Ultra Ball1,200High
Potion300Low
Super Potion700Medium
Hyper Potion1,200High
Revive1,500High
Antidote100Low
Paralyze Heal200Low
Awakening250Low

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 BandTotal SalesOrder Count
High2,289,000487
Low3,904,6001,542
Medium3,223,200711
Total9,416,8002,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:

StoreTotal SalesBig Store Sales
Cerulean City1,666,5501,666,550
Goldenrod City2,280,5502,280,550
Lilycove City1,373,450
Slateport City1,598,5501,598,550
Violet City1,083,100
Viridian City1,414,600
Total9,416,8009,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?

UseWhen
IFOne condition with two possible results
SWITCHOne 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.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.