Logical Functions and Variables
Meera wants every branch, product and day labelled Strong, Steady or Weak by revenue. Arjun's first draft nests one IF inside another and calls [Total Revenue] three times. It works, but it is hard to read and does the same sum over and over. SWITCH and variables clean up both problems.
The rule
Name a value once with VAR, then test it; the first true test wins.
Logical functions:
IF ( condition, value_if_true, value_if_false ). Leave out the third argument and a false condition returns BLANK.AND ( a, b ),OR ( a, b )andNOT ( a ). AND and OR take exactly two arguments; for more, use the&&operator.SWITCH ( TRUE (), test1, result1, test2, result2, …, else_result )checks the tests in order and returns the result of the first true one.COALESCE ( [Total Revenue], 0 )returns the first value that is not blank, which is handy for showing 0 instead of an empty cell.
Variables: VAR Name = expression stores a value, and RETURN gives the final result. A variable is calculated once, where it is defined, and can then be used as often as you like. That makes formulas shorter, easier to read and often faster. To debug, temporarily RETURN a single variable and look at its value.
Revenue Status =
VAR Rev = [Total Revenue]
RETURN
SWITCH (
TRUE (),
Rev >= 300, "Strong",
Rev >= 200, "Steady",
"Weak"
)| Row in a visual | Total Revenue | Revenue Status |
|---|---|---|
| Pune | 240 | Steady |
| Mumbai | 360 | Strong |
| Masala Chai | 180 | Weak |
| Green Tea | 280 | Steady |
| Samosa | 140 | Weak |
| 5 January | 100 | Weak |
| 6 January | 200 | Steady |
| 7 January | 300 | Strong |
Look closely at 6 and 7 January: >= includes the boundary, so exactly 200 is Steady and exactly 300 is Strong.
The shortcut
Write the tests from the highest threshold down, the same rule as a conditional column in Power Query. And whenever a formula repeats an expression, lift it into a VAR.
The variable
Rev = [Total Revenue]for Green Tea: orders 1003 and 1006, so 80 + 200 = 280. It is calculated once.
A trap to know: if the tests were written low to high, with Rev >= 200 first, every value of 200 or more would stop there as Steady, and nothing could ever be Strong.
Drills
Answer fast — trust the rule.
What does Revenue Status show for Mumbai?
What does Revenue Status show for 6 January, with revenue of exactly 200?
A measure reads Busy = IF ( [Total Qty] > 10, "Busy" ). What does it show for Pune, whose Total Qty is 9?
If the SWITCH tests were written with Rev >= 200 first and Rev >= 300 second, what would Mumbai show?
How many times is [Total Revenue] calculated in one evaluation of Revenue Status?
Remember
- Name a value once with VAR, then test it; the first true test wins.
- Order the tests from the highest threshold down, and lift repeated expressions into a VAR.
- Traps: IF without a third argument returns BLANK, and tests written low to high hide the top band.