Twitter Updates

    follow me on Twitter

    Tuesday, July 10, 2012

    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

    "Wrong Value"

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

    [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.
    [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.

    No comments: