Logical functions

Intermediate DAX in Power BI

Carl Rosseel

Curriculum Manager

Overview of logical functions

Logical functions act upon an expression to return information about the values or sets in the expression.

The most used logical functions are:

IF()
AND(), OR(), NOT()
SWITCH()

IF() is one of the most commonly used logic functions

Structure:

IF(<logical_test>, <value_if_true>, <value_if_false>)

Example:

Performance = IF([Total Sales] >= 50 000, "Target Reached", "Target Not Reached")

IF() is one of the most commonly used logic functions

Structure:

IF(<logical_test>, <value_if_true>[, <value_if_false>])

Example:

Performance = IF([Total Sales] >= 50 000, "Target Reached", "Target Not Reached")

Name	Total Sales
Jenny	48,431
Jane	76,528
Dwayne	24,167
Thomas	52,125

IF() is one of the most commonly used logic functions

Structure:

IF(<logical_test>, <value_if_true>[, <value_if_false>])

Example:

Performance = IF([Total_Sales] >= 50 000, "Target Reached", "Target Not Reached")

Name	Total Sales	Performance
Jenny	48,431	Target not Reached
Jane	76,528	Target Reached
Dwayne	24,167	Target Not Reached
Thomas	52,125	Target Reached

AND(), OR() & NOT() operators

All three operators return TRUE or FALSE as the output.

AND(<logical1>,<logical2>)
- Returns TRUE if both conditions are TRUE
- Example: AND(5 < 4, 5 < 6) = AND(FALSE, TRUE) = FALSE
OR(<logical1>,<logical2>)
- Returns TRUE if at least one condition is TRUE
- Example: OR(5 < 4, 5 < 6) = OR(FALSE, TRUE) = TRUE
NOT(<logical>)
- Changes TRUE to FALSE and vice versa
- Example: NOT(OR(5 < 4, 5 < 6)) = NOT(TRUE) = FALSE

AND(), OR() & NOT() operators

AND can be replaced by &&

AND(5 < 4, 5 < 6) = 5 < 4 && 5 < 6

OR can be replaced by ||

OR(5 < 4, 5 < 6) = 5 < 4 || 5 < 6

The power of SWITCH()

Evaluates an expression against a list of values and returns one of multiple possible result expressions.

SWITCH(<expression>, <value>, <result>[, <value>, <result>] ... [, <else>])

Often preferred over nested IF() functions

Performance = SWITCH(TRUE, 
[Total_Sales] < 25 000, "Poor",
[Total_Sales] < 50 000, "Below expectations",
[Total_Sales] < 75 000, "Above expectations",
"Exceptional")

The power of SWITCH()

Performance = SWITCH(TRUE, 
[Total_Sales] < 25 000, "Poor",
[Total_Sales] < 50 000, "Below expectations",
[Total_Sales] < 75 000, "Above expectations",
 "Exceptional")

Name	Total Sales
Jenny	48,431
Jane	76,528
Dwayne	24,167
Thomas	52,125

The power of SWITCH()

Performance = SWITCH(TRUE, 
[Total_Sales] < 25 000, "Poor",
[Total_Sales] < 50 000, "Below expectations",
[Total_Sales] < 75 000, "Above expectations",
 "Exceptional")

Name	Total Sales	Performance
Jenny	48,431	Below Expectations
Jane	76,528	Exceptional
Dwayne	24,167	Poor
Thomas	52,125	Above expectations

The power of SWITCH()

DISCOUNT = SWITCH([Clothing Type], 
               "Shoes", 0.15,   
               "Pants", 0.20,
               "Belts", 0.30, 
               "T-shirt", 0.25)

Clothing Type
Shoes
Pants
Belt
T-shirt

The power of SWITCH()

DISCOUNT = SWITCH([Clothing Type], 
               "Shoes", 0.15,   
               "Pants", 0.20,
               "Belts", 0.30, 
               "T-shirt", 0.25)

Clothing Type	Discount
Shoes	15%
Pants	20%
Belt	30%
T-shirt	25%

Let's switch up!

Intermediate DAX in Power BI