DAX

When PowerPivot was announced a couple of years back, I was one among the many who started feeling insecure on having to learn a new trade. I could have sat there and kept on cribbing, or picked myself up and started my baby steps towards the new technology. As Hillary Clinton said, the challenges of change are always hard. It is important that we begin to unpack those challenges and realize that we each have a role that requires us to change and become more responsible for shaping our own future. I am glad that I gave PowerPivot a chance and kept on learning (it sure helps sitting next to a passionate PowerPivot supporter like Javier Guillen!), else I wouldn’t have realized how much useful it is to me. Agreed that it has it’s fair sets of limitations, but as the community grows and the technology matures, we will see that many of it gets resolved or have workarounds. For eg, if you had asked me before on whether there was any way in PowerPivot to group all the row values into a single delimited row, I would have said no. But seeing this question in the PowerPivot forum, I decided to spend a little more time in the weekend and found a workaround, which I am sharing through this post.

Scenario
Let us say that we have a table which shows the sales by Country and State. The result should be a table which shows the sum of sales by country along with a delimited row of states as shown below

Solution
There is only one function (atleast as far as I know) in PowerPivot that returns a delimited list from multiple rows – PATH(). But the issue is that PATH() works only with a parent child hierarchy. So the trick for getting this piece done would be to build an artificial parent child hierarchy. Follow the steps below to get the solution:- 1) Make a calculated column called Rnk which will generate a running number of the states within a country.

=RANKX (
  FILTER ( All ( Table1 ), Table1[Country] = EARLIER ( Table1[Country] ) ),
    Table1[State],    ,    1,   DENSE
  )

This will allow us to uniquely identify each state value within a country.

2) Make a calculated column called ParRnk with the formula below

=IF (
  Table1[Rnk] <> 1,
  Table1[Rnk] – 1
)

This will allow us to get the parent node id of the parent child hierarchy.

3) Now, make a calculated column called ParState which will get the Parent State for the current row (based on the ParRnk column).

=CALCULATE (
FIRSTNONBLANK ( Table1[State], 1 ),
FILTER (
ALLEXCEPT ( Table1, Table1[Country] ),
    Table1[Rnk] = EARLIER ( Table1[ParRnk] )
  )
)

4) The final part of the solution is to make the delimited list column by using the path function where State is the child node and ParState is the parent node. But if we directly apply the PATH function in each row, we will not get the intended results as you can see in the image below.

What we need is the delimited list value for the row with the maximum rank in each country applied across all other rows in that country. For that, make a calculated column called ConcatStates with the formula below

=CALCULATE (
PATH ( Table1[State], Table1[ParState] ),
CALCULATETABLE (
FILTER ( Table1, Table1[Rnk] = Max ( Table1[Rnk] ) ),
ALLEXCEPT ( Table1, Table1[Country] )
  )
)

Now the results should look like below

Now make a new pivot table and drag and drop the Country, ConcatStates and Sales fields to see the intended results

Wasn’t that a fascinating workaround? If you want to play around, you can download the excel file from here.

Last week, I was going through the PowerPivot forums as usual and was answering a question on How to handle count of data with “valid to/from” dates in DAX. This was a classic case of the “Events in Progress” or finding the number of active events between a start date and an end date (In case you haven’t read, Chris Webb (blog | twitter) has got some great posts on the Events in Progress solutions using both DAX and MDX). I replied back with a possible solution and soon, Marco Russo (blog | twitter) came up with a much more elegant solution. But more than the solution, what caught my eye was that our results were not matching and after a few email exchanges with Marco (and a big thanks to Marco for taking time off to answer my questions), we came up to the conclusion that it was because of the difference in our interpretations of the Event in Progress. That is when I thought of classifying the different interpretations of the “Events in Progress” as well as extending Marco’s solution for the same.

In a typical Events in Progress problem, there are fact rows with a start and end date and each row denotes an event. The requirement would be to find the number of events that were in progress on any given date. At a date level, there is no ambiguity as the number of events in progress for a particular day, say D would be the number of those events whose start date is less than or equal to D and the end date is greater than or equal to D (Actually, you can further sub-categorize this into whether the start dates and end dates are inclusive or exclusive i.e. whether the start date is less than or less than or equal to D and whether end date is greater than or greater than or equal to D. For the sake of simplicity, we are assuming that both start and end dates are inclusive). However, the moment you start analysing on a level above the dates, say a month or a year, the question arises on which date of the month to use for comparing the start and end date. Should we be using the first date of the month? Or the last date of the month? Or should we be using a combination of both? There is no wrong answer here and the right definition completely depends on the business requirement. So let us look at the four possible combinations that are possible:-

To give an example, say we are analysing the events in progress for January 2012. For Scenario S2, this would mean the number of events which have a From Date (or start date) less than or equal to January 1 2012 (which is the minimum date for January 2012) and To Date (or end date) greater than or equal to 31 January 2012 (which is the maximum date for the January 2012). Here also, we can further sub-categorize whether the start and end dates are inclusive or exclusive but this is not done for the sake of simplicity and also because our solution can easily be modified by removing the equal to symbol. It might be easier to understand the relationship between the four scenarios with the help of a Venn Diagram.

As you can see, Scenario S3 is the superset and includes any event that was active on any date in the selected time period. Scenario S1 requires that the event started before the time period but can end during any day on or after the time period. Conversely, scenario S4 requires that the event started on or before the time period and ends on any day after the time period. Scenario S2 is the intersection of the scenarios S1 and S4 and requires that the event starts before the time period and ends after the time period. This means that those events starting or ending in the selected time period will not be counted. Let’s have a look at the four scenarios from a different visualization perspective.

Now that you have got a good idea of the different scenarios, let us look at some sample data that we can use for testing the solution. Given below is a simple fact table which contains Order Numbers with a From Date and To Date and also the Order quantity.

The PowerPivot data model that we are going to use is given below

I just have a simple calendar table (where I have just populated days from 2011 to 2013, again for simplicity) and the above Orders fact table. It is interesting to note that we will not be creating any relationships between the two tables as our solution does not require them. Now follow the steps below to get the solution for the four scenarios:-

1) Before we start, it might be a good exercise to review the original formula that I wrote and why we are moving to a different solution. The original formula I wrote was

OldMsr:=Calculate (
                  CountRows ( Fct ),
                  Filter (
                           Fct,
                          CountRows (
                                          Filter (
                                                  Values ( Calendar[DateKey] ),
                                                  ( Calendar[DateKey] >= VALUE ( Fct[FromDate] )  ) && ( Calendar[DateKey] <= VALUE ( Fct[ToDate] ) )
                                                  )
                                            ) > 0
                              )
                      )

To explain this formula, I am dividing it into 3 parts. The red highlighted area gives us the count of the fact rows which is the count of events in progress, provided the correct set of rows in the Fact table is passed as a table filter. Now to pass the correct table filter, we need to filter the fact table which is the part highlighted in orange and the condition to filter it is highlighted in yellow.  Since we don’t have any relations between the tables, what I am doing is to filter all the fact rows which has at least one row where there is a date greater than the From Date but less than the To Date. When we are analysing by month, what this will mean is that even if there is a single day in that month which satisfies the condition, include that fact row. This is exactly similar to our scenario S3. Now some of the problems associated with this:-

– Formula is difficult to understand and write
– Formula can’t be easily changed to that of other scenarios
– Formula doesn’t perform well

2) Now let us look at the formula which was given by Marco in the forums

S3:= Calculate (
  CountRows ( Fct ),
  Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Max ( Calendar[DateKey] )  ),
  Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Min ( Calendar[DateKey] )  )
                 )

You can already see how simple it is to understand. We are just passing the From dates which are less than the max of the time period and those To Dates which are greater than min of the time period as a table filter to the calculate statement and there is no need to filter the fact table explicitly. Not only that, it performs much better as it doesn’t have to parse through the entire fact table and just has to parse through two tables having the distinct values of From Date and To Date respectively (Always remember, it is faster to parse through two smaller tables than one big table). Now the best part is that this can be easily extended to the other scenarios as shown below

S1:= Calculate (
CountRows ( Fct ),
Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Min ( Calendar[DateKey] ) ),
Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Min ( Calendar[DateKey] ) )
)

S2:= Calculate (
CountRows ( Fct ),
Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Min ( Calendar[DateKey] ) ),
Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Max ( Calendar[DateKey] ) )
)

S4:= Calculate (
CountRows ( Fct ),
Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Max ( Calendar[DateKey] ) ),
Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Max ( Calendar[DateKey] ) )
)

Wasn’t that simple?

3) Now let us have a look at the results to make sure everything is coming correctly.

I have filtered for January 2012 and you can see that the dates in that table are showing the same values for all the five measures. Let us pick 10 January 2012 as an example. Only 11001, 11003 and 11004 suffice the conditions and hence the measures are all 3. The results come up correctly even if we look at a month or year level.

Let us take January 2012 as an example, so the max will be 31 Jan 2012 and minimum will be 1 Jan 2012. The order numbers which match the given conditions are given below and matches with the measure counts

S1 = 11001, 11002, 11003
S2 = 11001, 11003
S3 = 11001, 11002, 11003, 11004, 11005, 11006
S4 = 11001, 11003. 11006

4) Another benefit of this pattern is that it can easily be applied to other problems where you might need to find the sum or distinct count or any other aggregations between a from date and to date. Let us say, if I want to find the sum of Order Quantity between the from date and to date according to scenario S1, all I need to do is as follows

S1Sum:= Calculate (
Sum ( Fct[OrderQty] ),
Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Min ( Calendar[DateKey] ) ),
Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Min ( Calendar[DateKey] ) )
)

If you want to try this on your own, feel free to download the completed excel file from here.

As a Business Intelligence professional, I find it really fascin(/frustr)ating working with Excel users. You get exposed to a completely different way of thinking when you work with them, not to say that they are extremely smart and demanding. From the moment I started working with PowerPivot and interacting with Excel users, I knew it was not going to be easy tackling their questions and requirements. You simply cant escape by saying that the required feature is not available because they will push you till you find a workaround. This was the case when one of my users came back to me asking for a slicer which will dynamically help them to select the measures. I replied saying that the slicers are not intended for that purpose and should be used for filtering the data (and not for just selecting or deselecting values like Measures, which can be easily done from the Field List pane). However, the user pushed back saying that he would like to create a dashboard for the top management and didn’t want them to be using the Field List pane. That is when I had to put on my thinking cap. For the purpose of this post, I am going to use some simple mocked up data which involves three measures (Sales, Quantity and Profit) for country and year. Follow the steps below to recreate a slicer which can be used to select or deselect the measures in a pivot table:- 1) Create a table which will have the list of measure names needed in the slicer in the MeasureName column and their representative integer ids in the MsrId column. Import this table into PowerPivot using Linked Tables and name it as Msr. There is no need to make any relations with the existing tables, and Msr table should be a standalone table. 2) Now make a new measure called MsrValue with the formula given below MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
Sum ( Fct[Sales] ),
Min ( Msr[MsrId] ) = 2,
Sum ( Fct[Quantity] ),
Min ( Msr[MsrId] ) = 3,
Sum ( Fct[Profit] )
) This measure will check the value of the MsrId column, and then use the appropriate measure. However, it should be noted that when there are more than one value of MsrId (or if more than one measures are selected), only the measure having minimum value of MsrId would be displayed. So how will we display more than one measures on the pivot table? Read on. 3) With this very simple setup, we are ready to view the results in the Pivot table. Drag and drop MeasureName column into the Slicer, Year into the Column Labels and Country in the Row Labels. Then drag and drop MsrValue in the Values and we should have something similar to the image below. I have also included the fact rows above the pivot so that we can compare. Now there are a couple of problems with this pivot table. You would have noticed that the pivot table works correctly if only one measure is selected, but if there are more than one selections for the measure name, it will only show the measure with the minimum of the MsrId (eg, Sales in the image above). Also, we would like to see the name of the measure in the column above for clarity. To solve both of these problems, follow the next step. 4) Just drag and drop the MeasureName column in the Column Labels above the Year. Now you can see that multiple measures are displayed and also that the measure names are displayed in the column above. You can also change the formatting of the measures if needed MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
FORMAT ( Sum ( Fct[Sales] ), “$#,#0” ),
Min ( Msr[MsrId] ) = 2,
format ( Sum ( Fct[Quantity] ), “#,#0” ),
Min ( Msr[MsrId] ) = 3,
format ( Sum ( Fct[Profit] ), “$#,#0” )
) Luckily, that wasn’t as hard as I thought (which means that I could still use my evening free time to focus on more important problems like whether the chicken or egg came first into the world!). If you don’t have such important problems to focus on like me, you can download the completed file from this link and test it out on your own!

2. The difference of each driver from the leader (as of that race) should be displayed. Implementation 1. Build the PowerPivot model for the RaceData as shown in the diagram above. For simplicity, I have already created the model in the source file available for download here. 2. For creating the running total across the races, we will be following the technique detailed in my previous blog. For that, create 2 calculated columns in the RaceFact table called Points and Order respectively. =calculate(sum(Points[Points])) =calculate(sum(Races[Order])) Notice that the foreign keys in the fact table (Driver, Race, Position) are hidden as it is a best practice to hide the foreign keys. This way, the users will not get confused whether to use the columns in the master tables or the fact tables. 3. For finding the running total, we can create a new measure with the below formula RT_Points:=calculate(sum(RaceFact[Points]), filter(Allexcept(RaceFact,Driver[Driver]), RaceFact[Order]<=Max(RaceFact[Order]))) When this measure is used against the drivers on rows and races on columns, it will display the running total of the points for each driver across the races. 4. Now for getting the difference of each driver from the leader, we need a measure which will give us the points of the race leader at the end of every race. This measure would be equal to the max of the running total that we just created and that should be the same for all drivers for a particular race. Create the measure with the DAX formula given below MaxRT_Points:=calculate(maxx(values(Driver[Driver]), [RT_Points]), All(Driver[Driver])) The result is shown below 5. Now all we have to do to get the final requirement is to subtract the MaxRT_Points measure from the RT_Points measure as shown below DiffFromLeader:=[RT_Points] – [MaxRT_Points] The result is shown below 6. With a bit of formatting and adding a chart for the top 6 drivers, I can now clearly see how the battle for the top place has been going The final version of the PowerPivot file can be downloaded from here. It is interesting to see that Fernando Alonso has been the most consistent driver, as can be seen from the above chart, even though he is in second place. With this information, I am going to bet my money on Alonso winning the Drivers championship (and not because I am a biased Ferrari fan :P). What do you reckon?

Recently, I have been dealing a lot with business users and solving their problems. Even though I have been closely associated with the business in most of my previous projects, this project has given me a whole new perspective to the work I do. I am more used to the sort of work where I sit with the users, gather the requirements from them and then disappear to do my magic for at least a month. The users get to see the end result (and even the intermediate result in our scrum meetings) but they don’t get to see me working (at least not every minute of the day). In contrast, here I sit with the users from morning till evening and solve their problems along with them. We tackle the problem as a team – the users armed with their business knowledge and me with the technical knowledge. The active involvement of the users can be mainly attributed to the PowerPivot / self-service BI drive, and the company is already reaping a lot of benefits from this. It is in midst of one of these meetings that I encountered an interesting requirement – the capability to change the order of running totals.

Let me give a simplified version of the requirements below. Available Data We already have a PowerPivot file with the following tables:-

1) Product – has one column with Product names

2) CustomGroup – has one column with the CustomGroup names. Actually, this was a custom grouping of weeks like Late, Future, etc but for the purpose of this demo, we are just going to use values like G1, G2, etc. This is the field which is used for calculating the running totals, and the order in which the running totals should be calculated is specified in the Order column.

3) Stock – Will have the stock value for each Product.

4) Forecast – Will have the forecast quantity for a product and custom group.

You can download the source file (RT_Source.xlsx) from here. For the purpose of this post, the source data for these tables are sourced from linked tables in the file.

Requirement

1. Running totals of the forecast should be calculated across the CustomGroup dimension. The order in which the running totals are calculated should be easily configurable, as the order can be different for different users.
2. A report should be generated which shows the stock for each product as well as the forecast across the custom groups. However, the forecast should only be displayed if the stock would not be sufficient for it. For eg, consider the image below where the first table shows the actual data. The second table shows how the end result should look like. For P1, the forecast quantity in G1 is not displayed as it can be covered by the stock. However, from G2 onwards, all the quantities are displayed for P1. Similarly for P2, the forecast quantity for G3 is not displayed as it is less than the stock. However, for G4, only 5 is displayed as you have 125 as the forecast for G3 and G4 and you just have 120 as stock .

Implementation

The final PowerPivot report (RT.xlsx) can be downloaded from here. For implementing this from the source file, follow the steps below:-

1) I need to display Stock along with Product in the rows of the report. For that, I am going to use the product column from the Product table instead of the fact so that it filters both the stock and forecast tables. To avoid the cross join of Product[Product] and Stock[stock] columns, I am introducing Stock as a calculated column in the Product table with the formula below:-

=CALCULATE(sum(Stock[Stock]))

2) The running totals need to be done across the CustomGrp values, and hence make sure that the order column specifies the correct order in which they should be performed.

The order can be easily changed from the source sheet in excel (from where this linked table is made) in case the running total needs to be configured for other users.

3) In the forecast table, make a calculated column which will get the corresponding order for the CustomGrp for that row using the formula below

=RELATED(CustomGrp[Order])

4) Once we get the Order for that row, the running total can be made as a calculated column using the formula below

=Calculate (
Sum ( Forecast[Qty] ),
CalculateTable (
Filter (
AllExcept ( Forecast, Forecast[Product] ),
Forecast[Order] <= Max ( Forecast[Order] )
)
)
)

This formula gets evaluated for each row, and then filters out all the rows for that particular product where the Orders are less than the Order value in the current row. Then it calculates the sum of forecast quantity for the resultant filtered table, and this gets us our running total.

The running total can also be made as a measure (I have created a measure called Test in the RT.xlsx for this) but I chose to implement it as a calculated column so that the running totals are stored.

5) Now for just displaying the forecasts which are above the stock, I can create a new measure with the formula below:-

UnderstockQty:=If (
Sum ( Forecast[RT_Qty] ) <= Sum ( Stock[Stock] ),
Blank ( ),
Sum ( Forecast[Qty] )
)

Notice that I am using the running total of forecast to check against the stock and not just the forecast quantity. But this measure will not give us the final end result which is illustrated in the example below

You can see that for P2, we need to display 5 in G4 and not 75. For this, we need an additional check to see when was the first group value (or minimum sort order) when the forecast exceeded the stock.

6) Create a new measure to find the minimum Group value (or minimum order) in which the forecast exceeded the stock.

MinOrder:=Calculate (
Min ( Forecast[Order] ),
CalculateTable (
Filter (
AllExcept ( Forecast, Product[Product] ),
[RT_Qty] > Sum ( Stock[Stock] )
)
)
)

7) Now we can edit the measure to include the additional check. I am just creating a new measure called RemainingUndrStkQty so that we can compare the difference with UnderstockQty measure.

RemainingUndrStkQty:=If (
Sum ( Forecast[RT_Qty] ) <= Sum ( Stock[Stock] ),
Blank ( ),
If (
[MinOrder] = Min ( CustomGrp[Order] ),
Sum ( Forecast[RT_Qty] ) – Sum ( Stock[Stock] ),
Sum ( Forecast[Qty] )
)
)

Now you can see that we are able to get the desired results.

If you are the sort of person who observes carefully, then you would have noticed that I used a set for the group values in the columns. This was done so that if the user felt like changing the order for the running totals, then they can edit the set for changing the display order also. However, if you are using the new version of PowerPivot, a better idea might be to use the Sort by Column feature to sort the CustomGroup table by Order. Then you would be able to use the Group attribute directly in the columns and the changes to order would also be reflected immediately.

Now, to test whether your running total works if the order is modified, change the order column values in the reverse order for the groups as shown above, and you can see the result as well as the running totals change as shown below