PowerPivot Nuggets - Part 18 - SWITCH() and multiple expressions

SWITCH() is a new function introduced in PowerPivot v2.  SWITCH() is a great alternative for nested IF() functions in DAX.  Let's have look at a very simple example.

Let's say I want to categorise scores, 1 to 5 would become a C, 5 to 8 becomes a B and everything above 8 is an A.

In PowerPivot v1 we could write this with nested IFs as follows:

=IF([Score] >=1 && [Score] <5; "C";
          IF([Score] >= 5 && [Score] < 8; "B";
                 IF ([Score] >=8 && [Score] <=10; "A";
    "Wrong Value"
)))

I could write this shorter and more elegant but I want to show you that you can evaluate different independent expressions for each IF statement.  I really hate nested IFs.  I have seen DAX calculations with more than 30 nested IFs and trust me they are hard to debug.

So, enter SWITCH().

At first I was a little disappointed when I saw the syntax for the SWITCH() functions.

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

It looks like you can only have one expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).

This means that in our example the calculation would look something like

=SWITCH([Score];
1;"C";
2;"C";
3;"C";
4;"C";
5;"B";
6;"B";
7;"B";
8;"A";
9;"A";
10;"A";
"Wrong Value"
)

Easy to read, but not really a time saver.  I was hoping I could write something like this:

=SWITCH(
[Score] >=1 && [Score] <5; "C";
[Score] >= 5 && [Score] < 8; "B";
[Score] >=8 && [Score] <=10; "A";
"Wrong Value"
)

The expression above will raise an error.  However, recently I ran into a trick.  If you just pass TRUE() as the first argument you can actually evaluate different independent expressions.  This results in the following expression.
=SWITCH(TRUE();
[Score] >=1 && [Score] <5; "C";
[Score] >= 5 && [Score] < 8; "B";
[Score] >=8 && [Score] <=10; "A";
"Wrong Value"
)

So SWITCH(TRUE();expression1; value1; expression2; value2; expression3; value3; .... ElseValue)  is the trick for rewriting nested IFs in DAX.