Twitter Updates

    follow me on Twitter

    Friday, July 20, 2012

    PowerPivot Nuggets - Part 19 - Automate PowerPivot Refresh with Excel 2013

    Earlier this week Microsoft released the Office 2013 Customer Preview.  PowerPivot and Power View are now first class citizens in Excel, how cool is that.  I will blog about the BI features of Excel 2013 in the future.

    But today I want to share a nice long awaited feature for PowerPivot: Automate the refresh of the PowerPivot model.

    Excel 2013 now contains a Workbook.Model class that can be accessed via VBA code.  The following line of code will refresh your PowerPivot Model.

    Me.Model.Refresh

    If you want to automatically refresh the PowerPivot model when the workbook is opened, simply follow the following 2 steps.

    • Press ALT-F11 to open the Vusual Basic editor and then paste the following lines of code in the Code window for ThisWorkbook

      Private Sub Workbook_Open()
          If MsgBox("Do you want to refresh the PowerPivot Model?", vbYesNo) = vbYes Then
              Me.Model.Refresh
          End If
      End Sub
    •  Save the workbook as an Excel Macro Enabled Workbook (.XLSM)

     Next time you open the Excel file you will be prompted with the question "Do you want to refresh the PowerPivot Model".  Choose Yes, and Excel will automatically refresh the PowerPivot Model and all the Pivottables in the workbook.

    How cool!!!

    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

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