DAX IF Statement, Nested IF and SWITCH(TRUE) Explained
· 6 min read
A DAX IF statement takes a condition, a value if true, and an optional value if false. If you leave out the third argument, DAX returns BLANK when the condition is false. For more than two outcomes, use SWITCH ( TRUE (), ... ) instead of nesting IFs: it checks conditions top to bottom and returns the first match.
-- Calculated column on Sales
Order Size =
IF ( Sales[Quantity] >= 6, "Bulk", "Standard" )
-- Three or more outcomes
Order Size Band =
SWITCH (
TRUE (),
Sales[Quantity] >= 8, "Bulk",
Sales[Quantity] >= 4, "Medium",
"Small"
)The examples below use Brightline Supply Co. data and show the results, including the condition-order mistake AI assistants make with SWITCH(TRUE()).
The example data
Brightline's model has a Sales fact table linked to Customers, Products and Date. Eight order lines:
| OrderID | Customer | Quantity | Status | Line Revenue |
|---|---|---|---|---|
| 50001 | Harbor & Pine Hotels | 4 | completed | 96.00 |
| 50002 | Lakeside Studios | 10 | completed | 49.50 |
| 50003 | Pioneer Studios | 3 | completed | 75.00 |
| 50004 | Harbor & Pine Hotels | 2 | completed | 112.10 |
| 50005 | River Print Co | 6 | cancelled | 84.00 |
| 50006 | Lakeside Studios | 5 | completed | 99.50 |
| 50007 | River Print Co | 8 | completed | 180.00 |
| 50008 | Pioneer Studios | 2 | refunded | 48.00 |
IF in a calculated column
In a calculated column, IF runs once per row, so it can read the row's columns directly. Adding the two columns from the top gives:
| OrderID | Quantity | Order Size | Order Size Band |
|---|---|---|---|
| 50001 | 4 | Standard | Medium |
| 50002 | 10 | Bulk | Bulk |
| 50003 | 3 | Standard | Small |
| 50004 | 2 | Standard | Small |
| 50005 | 6 | Bulk | Medium |
| 50006 | 5 | Standard | Medium |
| 50007 | 8 | Bulk | Bulk |
| 50008 | 2 | Standard | Small |
Because these are columns, you can put them on a chart axis or a slicer and split any measure by them. If you are unsure whether you need a column or a measure, read measure vs calculated column.
DAX nested IF
The same three bands with nested IFs:
Order Size Band =
IF (
Sales[Quantity] >= 8, "Bulk",
IF ( Sales[Quantity] >= 4, "Medium", "Small" )
)It works, but each extra band adds another level of brackets. Two levels is readable. Four is where mistakes creep in. That is the main reason to switch to SWITCH.
DAX SWITCH: matching one value
The basic form of SWITCH compares one expression against a list of values, like a lookup:
Region Manager =
SWITCH (
Customers[Region],
"North", "Elena Petrova",
"South", "Marcus Hill",
"East", "Priya Raman",
"West", "Daniel Ortiz",
"Unassigned"
)The last argument is the default when nothing matches. In a real model, a small Regions table with a relationship is better than hard-coding names, because people change jobs and formulas do not update themselves.
SWITCH TRUE: ranges and mixed conditions
SWITCH ( TRUE (), ... ) evaluates each condition in order and returns the result of the first one that is TRUE. Use it for ranges, or when conditions test different columns. Here it is in a measure that puts customers into tiers by completed revenue:
Completed Revenue =
CALCULATE ( [Revenue], Sales[Status] = "completed" )
Customer Tier =
SWITCH (
TRUE (),
[Completed Revenue] >= 200, "Gold",
[Completed Revenue] >= 100, "Silver",
"Bronze"
)| Customer | Completed Revenue | Customer Tier |
|---|---|---|
| Harbor & Pine Hotels | 208.10 | Gold |
| Lakeside Studios | 149.00 | Silver |
| Pioneer Studios | 75.00 | Bronze |
| River Print Co | 180.00 | Silver |
| Total | 612.10 | Gold |
Look at the total row. The measure is evaluated for the total as well, with no customer filter, and 612.10 is above 200. "Gold" for all customers combined means nothing. Hide it with ISINSCOPE:
Customer Tier =
IF (
ISINSCOPE ( Customers[CustomerName] ),
SWITCH (
TRUE (),
[Completed Revenue] >= 200, "Gold",
[Completed Revenue] >= 100, "Silver",
"Bronze"
)
)There is no third argument on the IF, so the total row is BLANK.
The SQL equivalent of SWITCH(TRUE()) is CASE WHEN ... THEN ... ELSE ... END, which also stops at the first true branch. See CASE WHEN in SQL, and for spreadsheets, IF with AND and OR in Excel.
IF with multiple conditions: AND, OR
Combine conditions with && (and) and || (or):
Big Completed Line =
IF ( Sales[Status] = "completed" && Sales[Quantity] >= 8, "Yes", "No" )Only 50002 and 50007 return "Yes". The AND() and OR() functions exist too, but each takes exactly two arguments, so && and || are easier once you have three conditions. For "one of several values", IN is shorter: Sales[Status] IN { "cancelled", "refunded" }.
Zero if blank
A measure returns BLANK when there are no rows. To show 0 instead:
Completed Revenue (zero) =
COALESCE ( [Completed Revenue], 0 )IF ( ISBLANK ( [Completed Revenue] ), 0, [Completed Revenue] ) does the same. Be careful: once a measure never returns BLANK, visuals show every customer or product, including ones with no sales at all. Use it on cards, not on large tables.
Common mistakes
SWITCH(TRUE()) conditions in the wrong order
Ask an AI assistant for the tier measure and you will sometimes get the conditions from smallest to largest:
Customer Tier =
SWITCH (
TRUE (),
[Completed Revenue] >= 100, "Silver",
[Completed Revenue] >= 200, "Gold",
"Bronze"
)No error, and every value looks reasonable. But Harbor & Pine Hotels (208.10) now shows "Silver", because 208.10 is already >= 100 and SWITCH stops at the first TRUE. In this data no customer can ever be Gold. How to spot it: with >= thresholds, the largest must come first (with <=, the smallest). Then check one value near each boundary by hand. A tier that never appears in the visual is the warning sign.
Using a bare column in a measure
Order Size = IF ( Sales[Quantity] >= 6, "Bulk", "Standard" )As a calculated column this works. As a measure it fails with "A single value for column 'Quantity' in table 'Sales' cannot be determined." A measure has no current row. Either make it a column, or aggregate first, for example IF ( SUM ( Sales[Quantity] ) >= 6, ... ).
Mixing data types in the branches
IF ( Sales[Quantity] >= 6, "Bulk", 0 ) returns text in one branch and a number in the other. A calculated column rejects it ("Expressions that yield variant data-type cannot be used to define calculated columns"). Make every branch the same type.
Forgetting the total row
As shown above, a measure that labels rows also runs on the total. Wrap it in ISINSCOPE or HASONEVALUE when the label only makes sense per row.
FAQ
How do I write an IF statement in DAX?
IF ( <condition>, <value if true>, <value if false> ). In a calculated column you can use columns directly. In a measure, wrap columns in an aggregation such as SUM or use other measures.
How do I do else if in DAX?
DAX has no ELSE IF keyword. Nest another IF in the false argument, or use SWITCH ( TRUE (), condition1, result1, condition2, result2, default ).
What does SWITCH(TRUE()) do in DAX?
It compares TRUE against each condition in order and returns the result for the first condition that is TRUE. The final argument without a condition is the default.
Is SWITCH faster than nested IF?
For most models the difference is small. Choose SWITCH because it is easier to read and to check, not for speed.
How do I handle multiple conditions in a DAX IF?
Use && for AND and || for OR inside the condition, such as IF ( Sales[Status] = "completed" && Sales[Quantity] >= 8, "Yes", "No" ).
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Price bands with SWITCH(TRUE())” →