1. Logical functions
Welcome! My name is Carl and I'll be one of your instructors in this course. Are you ready to learn about logical functions?
2. 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 in Power BI are the IF(), AND, OR, NOT, and SWITCH() functions. Note these aren't the only logical functions in Power BI, but we'll focus on these in the video.
3. IF() is one of the most commonly used logic functions
The IF() function has 3 arguments. It starts with a logical test, followed by the value if true, and ends with an optional argument if the value is false.
4. IF() is one of the most commonly used logic functions
Let's look at an example using the IF() function. If total sales is above 50000, we want to display "target reached", otherwise we display "target not reached".
5. IF() is one of the most commonly used logic functions
If we add performance to the table, we see that Jenny and Dwayne receive a performance rating of "target not reached" because their total sales are below 50000.
6. AND(), OR() & NOT() operators
The AND() function takes 2 logical values you want to test as arguments. It returns TRUE if both conditions are TRUE. Let's wrap two simple comparisons - is 5 smaller than 4, and is 5 smaller than 6 - inside the AND() statement. 5 is not smaller than 4, so the first argument returns FALSE. 5 is smaller than 6 however, so the second argument returns TRUE. Applying the AND() function to FALSE and TRUE returns FALSE.
The OR() statement is similar to the AND() statement, but instead it returns TRUE if at least one condition is TRUE. If we apply the OR() function to the previous arguments we get TRUE, because one of the two arguments was TRUE. The NOT() function changes TRUE to FALSE and vice versa. So, if we wrap the previous OR() function inside NOT(), the result is FALSE.
7. AND(), OR() & NOT() operators
The AND() function can be replaced by a double ampersand. Similarly can you replace the OR() function by double vertical bars.
8. The power of SWITCH()
SWITCH() is an extremely handy Power BI function you should know how to use.
It evaluates an expression against a list of values and returns one of several possible result expressions. Its first argument is an expression, followed by multiple value-result pairs, before being closed by an optional else statement. This function is often preferred over nested IF() functions.
Note the SWITCH() statement evaluates the arguments in order. So only if the first expression is FALSE, the second expression will be evaluated, and so on. A SWITCH() function often starts with TRUE. The function will check if Total_Sales is below 25000, and if it's TRUE, it will return the result poor. If false, the statement proceeds to the next value result pair, and checks if Total_Sales is below 50000. If that's true it will return "Below expectations", otherwise we move on to the next value result pair.
9. The power of SWITCH()
Earlier, we applied an IF() function to see if the sales target reached 50,000. We apply the SWITCH() function to the same table.
10. The power of SWITCH()
The SWITCH() function returns Below expectations for Jenny, because her total sales were below 50000, but above 25000. Jane got an exceptional performance rating because her sales were above 75000.
11. The power of SWITCH()
So far, we've used SWITCH() with TRUE as the first argument. This is common, but not the only case. This function creates a new DISCOUNT column, and checks the clothing type value to return the corresponding result.
12. The power of SWITCH()
The discount rate for shoes is 15%, for pants 20% and so on.
13. Let's switch up!
It's time to try the SWITCH() function and other logical functions for yourself! Have fun!