SYNTAX: IF(logical test, value if true, value if false)
An IF statement is composed of the following
If the logical test is true, it returns the value or string you specify.
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
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")
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.
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)
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.
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.