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.

    Friday, March 30, 2012

    PowerPivot Nuggets - Part 17 - Visualize KPI Trends

    KPIs are a nice new feature in PowerPivot 2012.  I blogged about KPIs before.    In PowerPivot KPIs have a value, a target and a status.  Compared to KPIs in full blown Analysis Services OLAP cubes we are missing the possibility to visualize the trend of the KPI.

    In this nugget I will show you a trick how you can visualize the trend of a KPI.  Let's have a look at this example.
    First of all we need to find the value of the Margin KPI for the previous quarter.  This pretty straightforward with the following calculated measure using the CALCULATE function:

    CALCULATE(SalesAndCosts[Margin],PREVIOUSQUARTER(Calendar[Date]))

    We calculate the margin, and change the filter context to the previous quarter.

    Next we can check if this value is lower than the margin for the current month.  If this is the case we return 1, otherwise we return -1.

    IF(CALCULATE(SalesAndCosts[Margin],PREVIOUSQUARTER(Calendar[Date])) < SalesAndCosts[Margin]
    , 1,
    -1)

    This will result in the following pivot table with 1s and -1s.


    By using conditional  formatting we can make the table visually more attractive.  Choose Conditional Formatting from the Home ribbon, Icon Sets, 3 arrows.


    Next, choose Conditional Formatting  again, click Manage Rule, Edit Rules and change the following parameters:
    • Check the checkbox Show Icon Only.
    • Green when value  is > 0 (Number)
    • Amber when <0= and >= 0 (Number)
    • Red when < 0


    This the final result.


    This workaround works pretty fine, disadvantage is the fact that the icon style is not saved with the KPI.  You need to repeat the conditional formatting on every new pivot table.

    Thursday, February 09, 2012

    PowerPivot Nuggets - Part 16 - Berekening Voordeel Alle Aard (VAA) Bedrijfswagens

    In Belgium there is a lot of buzz going around the higher taxes on company cars.  In Dutch it's called "Voordeel Alle Aard".  The goverment changed the way this tax is calculated.  Everybody with a company car in Belgium is now trying to find out wether he will pay more or less taxes.  Although we know the answer in advance :-)

    Time to put this into a PowerPivot workbook.  The workbook uses the concepts I described in my previous blogpost PowerPivot Nuggets - Part 15 - What If Analysis with Slicers.

    The tax used to be calculated on two parameters: CO2 emissions of your car and the distance between your house and your workplace.

    In DAX the formula looks like this:

    IF(HASONEVALUE(CO2) && HASONEVALUE(CatalogusPrijs) && HASONEVALUE(ForfaitAfstand) &&HASONEVALUE(Leeftijd)

    ;VALUES(CO2[CO2 Uitstoot])*0,00237 * VALUES(ForfaitAfstand[ForfaitAfstand 2011])
    ;BLANK())

    The new formula is more complex.  CO2 emissions, age and the list price of the car play a role. In DAX it looks something likes this:

    IF(HASONEVALUE(CO2) && HASONEVALUE(CatalogusPrijs) && HASONEVALUE(ForfaitAfstand) &&HASONEVALUE(Leeftijd)
    ;
       (( VALUES(CatalogusPrijs[CatalogusPrijs])
       * (((( VALUES(CO2[CO2 Uitstoot])-95)*0,1)+5,5)/100))*0,857143)
       * VALUES(Leeftijd[Pct])
    ;BLANK())

    Since the "Voordeel Alle Aard" is minimum 1200 EUR/year I need to write a wrapper around this calculation.

     Jaarlijkse Bijdrage Nieuw:=IF(Bijdrage[Jaarlijkse Bijdrage Nieuw Hidden] < 1200;1200; Bijdrage[Jaarlijkse Bijdrage Nieuw Hidden])

    Final step is to create a workbook with 3 slicers (Home-Work Distance, List-Price of the Car and CO2 Emission).  By choosing the right values for your car the workbook will calculate the old and new "Voordeel Alle Aard" and the difference between the two.


    You can download the workbook here. You need at least Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010 RC0.


    THIS WORKBOOK IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL FOR POWERPIVOT DEMO PURPOSE, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED.

    PowerPivot Nuggets - Part 15 - What If Analysis with Slicers

    Relationships are one of the cornerstones of PowerPivot.  In general every PowerPivot workbook should have relationships defined in the model.  However I found some very nice use cases where it makes sense not to define any relationships.

    Let's take a look at this little example. Suppose we have a table with articles, cost prices and sales prices.


    I want to see what happens with my margin (Sales Price - Cost Price) when I start giving discounts.   I create a second Discount table with possible discounts.  I can't create a relationship between the two tables because they don't have any columns in common.


    I place the discounts on a slicers and now I  need to find the "selected discount".  Since there's no relationship between the tables the built-in filter concepts of PowerPivot don't apply here.

    The DAX VALUES() function is my rescue.  VALUES() returns a one-column table that contains the distinct values from the specified column.  It doesn't make sense to calculate the discounted sales price when more than one discount is selected .  So I need to write a little wrapper around the VALUES() function to return BLANK() if more than one values is selected.



    SelectedDiscount := 
               IF(HASONEVALUE(Discount[Discount])=1;
                          VALUES(Discount[Discount])
                         ;BLANK())


    Now calculating the discounted value is very simple: SUM(Article[SalesPrice])*(1-VALUES(Discount[Discount]))


    DiscountedPrice :=
               IF(HASONEVALUE(Discount[Discount]);
                          SUM(Article[SalesPrice])*(1-VALUES(Discount[Discount]))
                          ;BLANK())

    And similar for the margin after discount in percentage:

    MarginAfterDiscount:= 
                          IF(HASONEVALUE(Discount[Discount]);
                          (Article[DiscountedPrice]-SUM(Article[CostPrice]))/SUM(Article[CostPrice])
                          ;BLANK())

    Now by simply clicking on the discount slicer I can see the effect of the discounts on my margin.





    Wednesday, February 01, 2012

    PowerPivot Nuggets - Part 14 - Generate a list with all your DAX calculations

    As your PowerPivot workbooks grow larger and contain more calculation the urge for some kind of documentation becomes stronger.  In this PowerPivot Nugget I will describe a simple way to generate a list with all your DAX calculations.

    The technique uses the internal dynamic management views of Power Pivot.  The dynamic management views are internal objects in PowerPivot that hold a lot of useful information about your PowerPivot model.  In his blogpost Querying PowerPivot DMVs from Excel Chris Webb describes step by step how to use them.

    I will show you a shortcut that only takes a few mouse clicks.

     Step 1 - The preparation - Create an .ODC file with the connection and query to the embedded PowerPivot model

    Option 1
    • Download the connection file from Skydrive "PowerPivot Get All DAX statements.odc"
    • Make sure you have the extension .odc correct
    • Save it in an easy to remember location (e.g. My Data Sources).
    Option 2
    Create the ODC file from scratch as described in the blogpost by Chris.  There are 2 important parts in the connection file you need the change.

    The connection string to the internal PowerPivot model:

    Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;
    Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;
    Optimize Response=3;Cell Error Mode=TextValue

    and the query to retrieve the DAX expressions:

    SELECT DISTINCT [TABLE],  OBJECT_TYPE, OBJECT,    EXPRESSION
    FROM $system.discover_calc_dependency 
    WHERE OBJECT_TYPE = 'MEASURE' 
    OR OBJECT_TYPE  = 'CALC_COLUMN'
    ORDER BY 1


    Step 2 - Generate the DAX documentation

    Open a PowerPivot workbook and create a new blank sheet.
    In the Data ribbon, choose Get External Data, Existing Connections.


    Browse to the "PowerPivot Get All DAX statements.odc " file you created in Step 1.


     Click OK and this is the result



    In the future Excel will remember the connection to the ODC file and it will only take you 3 mouse clicks to get an updated list of all your DAX calculations.
        1) Get External Data
        2) Double click on  PowerPivot Get All DAX statements in the Existing Connections dialogue box.




    A real time saver!!