November 6, 2020
DAX function IF
For Power BI version 2.76.5678.782 (December 2019).
Function IF() evaluates the expression to TRUE or FALSE and returns the value specified for each outcome.
IF(LogicalTest, ValueIfTrue, [ValueIfFalse])
|LogicalTest||Expression evaluated as TRUE or FALSE|
|ValueIfTrue||Value returned for TRUE|
|ValueIfFalse||Value returned for FALSE (optional)|
We can use any expression in logical test. We often use different comparison operators.
|<=||Less or equal to|
|>=||More or equal to|
We can test more conditions using and or or operator.
Example: one condition
Supplies table shows current storage of office supplies.
We want to add a column that would state »Order« for articles that gave Quantity less than 200 and »Don’t order« for all else. We select New Column and enter the following formula.
IF_Example = IF(Supplies[Quantity] < 200, “Order”, “Don’t order”)
Example: multiple conditions
We want to order all articles that have quantity less than 200, but we don’t want to order pens, regardless of quantity. We add another condition.
IF_Example = IF(Supplies[Quantity] < 200 && Supplies[Name] <> “Pen”, “Order”, “Don’t order”)
Example: multiple IF conditions
We often want to return a third value. In this case we add an IF function to our formula. Let’s say we want to create a column that would state:
- »Order« for articles that have quantity less than 200
- »Sell back« for articles that have quantity equal or more than 1000
- »Don’t order« for all else
We add a new column and enter the following formula.
IF_Example = IF(Supplies[Quantity] < 200, “Order”, IF(Supplies[Quantity] >= 1000, “Sell back”, “Don’t order”))