POWER BI: Modeling

Summary of Basic Logical Functions

List of Basic Logical Functions
  • IF
  • AND
  • OR
  • NOT
The IF FUNCTION:

SYNTAX: IF(logical test, value if true, value if false)

An IF statement is composed of the following

  • A logical test
  • Result if true
  • Result if false

If the logical test is true, it returns the value or string you specify.

Example:

This Calculated Column will check if the state is in New York:

                        State Known For = IF(TransactionData[State] = "New York", "The Empire State", "Other")
	             

If this condition is true, it will return "The Empire State", otherwise it will return "Other". You can specify any value you like or leave the third argument empty as it is optional.

However, what if we want to check multiple states? Well we could use nested IF functions but this is unnecessarily messy. Instead use the SWITCH function

Using Logical AND

A Venn diagram below illustrates AND



Event A and Event B are denoted as circles. The intersection of two events A and B (also written as A && B) is the shaded area in the diagram above.

AND is often used in logical expressions to check if both conditions A and B are true.

This example checks if the state is New York and if profit is more than 10,000.

                        Business Good in NY = IF(
                                AND(TransactionData[State] = "New York", TransactionData[Profit] > 10000),
                                "Doing Well in the Empire State","Other"
                                )
	           

or alternatively

                        Business Good in NY = IF(
                                TransactionData[State] = "New York" && TransactionData[Profit] > 10000,
                                "Doing Well in the Empire State","Other"
                                )
	           

Both conditions need to be true in order to return the result "Doing Well in the Empire State".
Note: the second method is strongly preferred (AND function is rather limited in that it only takes two arguments).

For example with the && operator we can easily string together as many requirements as needed:

                      ROI above 80% AND Revenue > 1000000 AND Profit > 100000 = IF(
                                 TransactionData[ROI] > 0.8 &&
                                 SUM(TransactionData[Revenue]) > 1000000 && 
                                 SUM(TransactionData[Total Profit]) > 10000,
                                 "Business is GOOD", "It's Okay")
	           
Using Logical OR

A Venn diagram below illustrates OR



The union of events is denoted A or B (also written as A || B) and is shaded in the diagram above. A or B is True if either A is true, B is true or both A and B are true

OR is often used in logical expressions to check if one or more events are true.

For example, let's check to see if the state is New Jersy or New York:

                       New Jersey || New York = IF( 
                            OR(TransactionData[State] = "New Jersey", TransactionData[State] = "New York"),
                            "New York or New Jersey","Other State"
                            )
	         

or alternatively

                         New Jersey || New York = IF(
                                TransactionData[State] = "New York" || TransactionData[Profit] > 10000,
                                "Doing Well in the Empire State","Other"
                                )
	           

Again, the operator || is strongly preferred.

Using NOT

A Venn diagram below illustrates A and NOT B



Used to check if the condition is NOT True:

                   Isn't The Empire State = IF(NOT(TransactionData[State] = "New York"),True, False)       
	           
Combining What We Learned
                        Wacky Business Good Criteria = IF(
                             TransactionData[ROI] > 0.8 || 
                             SUM(TransactionData[Revenue]) > 1000000 || 
                             SUM(TransactionData[Total Profit]) > 0.5*SUM(TransactionData[Revenue]),
                             "Business is GOOD", "It's Okay")   
	           

This example is slightly more complex. Lets break it up. It will return "Business is Good" if any of the following conditions are True. In this case we have three conditions.

  • ROI greater than 80%
  • Revenue greater than $1,000,000
  • Profit greater than half of the Total Revenue

The following is a calculated column that acts as filter to return True IF NOT a holiday AND State is in New Jersey OR New York

            
            NON-Holidays AND (New Jersey OR New York)) = IF(
	            NOT(TransactionData[Holiday]) && 
	            (TransactionData[State] = "New Jersey" || TransactionData[State] = "New York"),
	            True, False)
           
           

See also Calendar Dates on how to make a Holiday filter in Power BI

Up Next: The Switch Function.



Dash-Intel is a Power BI and Tableau resource site for data visualization and building BI dashboards.

Data Analyst & Consultant

Copyright 2015-2023 Dash-Intel.com Terms