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.

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.

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.

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!!

PowerPivot Nuggets - Part 10 - KPIs

KPIs are another Analysis Services concept coming to PowerPivot. Creating KPIs is pretty straight forward.

In the example below I have table with Sales and Costs. Let's say we want to have a 5% margin.

KPIs in PowerPivot are based on a meusure. So, first thing to do is create a measure to calculate the margin.


Step 2 is setting the KPI target values. Target values can again be based on a measure or as in this simple example on a fixed value.


Step 3 is setting the tresholds and finally we choose an icon style.

And that's it, our KPI is ready to use!

PowerPivot Nuggets - Part 8 - Parent Child Relations

Parent Child hierarchies like organization hierarchies and account structures are very common in reporting environments. PowerPivot V2 ships with a set of new DAX functions which allows us to leverage this in PowerPivot.

Let's have a look at our dimEmployee table. This table has a parent-child relations (ParentEmployeeKey --> EmployeeKey). As you can see in the screenshot below; you cannot create a relationship between the two columns in PowerPivot. Just like with roll-playing dimensions PowerPivot uses a different approach than Analysis Services.



In PowerPivot Parent Child relatinships are managed through DAX. Let's focus on employee John Campbell and play around with some DAX functions.



First function is PATH. PATH returns a delimited text string with the EmployeeKey of all the parents of the current EmployeeKey, starting with the earliest and continuing until the current.

=PATH([EmployeeKey],[ParentEmployeeKey])

John Campbell's manager is Peter Krebs, and Peter Kreb's manager is Ken Sánchez. If we look at the corresponding Employekeys this results in 112|23|20.



Second function is PATHLENGTH(). PATHLENGTH(PATH([EmployeeKey],[ParentEmployeeKey])) will return the length of the path. In John Campbell's case this is 3.



With PATHCONTAINS() we can check if a particular key exists in the path.

=PATHCONTAINS(PATH([EmployeeKey],[ParentEmployeeKey]),23) will return TRUE if EmployeeKey 23 is found anyware in the levels above the current Employee. In the case of John Campbell this will return TRUE.

PATHITEM AND PATHITEMREVERSE allows us to pick out a particular level in the hierarchy.



PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the top. PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the bottom.

In the case of John Campbell this is two times 23.

The last function we need to peek at is LOOKUPVALUE which allows us to lookup a particular column value for a given search criteria. The following DAX statement will return the manager's name.

=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2))

For John Campbell this is Peter Krebs.



If we now combine LOOKUPVALUE, PATH, and PATHITEM we can turn this parent-child dimensions into a natural hierarchy by creating a column for each level:

=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),1))
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2))



This approach is very similar to the concept used by BIDSHelper's Parent-Child Dimension Naturalizer for Analysis Services.

Next step is to create a hierarchy in the diagram view and create a pivottable.


This nugget was written on CTP3. One thing that is missing here is an easy way to hide the "empty levels". E.g. Peter Krebs is on level 2, so level 3, 4, 5, .. are empty for Peter Krebs.
In Analysis Services we solve this by setting the HideMemberIf property. Alberto Ferrari blogged a workaround for this in his post on Parent Child relationships. Alberto also opened an item on Microsoft Connect. Let's vote for this feature request and hope it makes it in the RTM version.

The longest nugget so far on a very cool BI concept coming to PowerPivot.

PowerPivot Nuggets - Part 7 - Multiple Relationships

Let's dive a little deeper into relationships today. In PowerPivot V1 we could only create one relationship between two tables. Good news coming to V2. We can now create multiple relationships between tables.

Let's have a look at our factInternetSales table. The table has OrderDateKey, DueDateKey and ShipDateKey. All three link to the DimDate table. As you can see in the screenshot below we can create a relationship for each one of them. However, only 1 of them can be the "active relationship". And that is the key-concept to understand in V2.



When you create a pivot table, PowerPivot will use the active relationship (on OrderDateKey). If you want see the Sales per DueDate or ShipDate you must create a DAX calculation and use the new USERELATIONSHIP function. This function will force PowerPivot to use a "non-active" relationships.

CALCULATE(SUM(FactInternetSales[SalesAmount]),USERELATIONSHIP(FactInternetSales[ShipDateKey], DimTime[TimeKey]))



This support for multiple relationships is a very nice addition to PowerPivot. But, personally I think Analysis Services' concept of roll-playing dimensions is more "user-friendly" than PowerPivot's concept of active relationships and USERELATIONSHIP.