Last time, I left our pivot table looking like the following image with a few minor problems that needed to be fixed including:
The month names were listed alphabetically rather than chronologically.
Some months may display error messages when no data exists in the current or previous month.
To fix the month order, I am going to return to the SSAS model.bim page and add a calculated column to the Date table. In this column I am going to use the MONTH() function to return the month number of the current row’s DateKey value. In the figure below, the first several rows of data occur in the month of July and thus the function returns the number ‘7’. In the case, the day and year do not matter. I just need to know the month number so I can sort the month names by the month number to get them in the correct order.
After creating the month number column, I go back to the CalendarMonthLabel column and select the entire column by clicking on the column header. Strictly speaking I would not have to select the column first. However, by doing so, the resulting dialog box automatically knows to use that column as the column that I want to display.
Next, open the Column drop down menu and select the Sort option. This opens a secondary fly out menu with sort options. From this menu, select the Sort by Column option as shown below.
The Sort by Column dialog already knows the column that I want to display (on the left side), but prompts me for the column name by which I want to sort the label. On the very bottom of the drop down list is the field I just added.
When I select the field to sort by and refresh my Excel pivot table, the data appears sorted correctly with the months now appearing in a chronological order. But, I still have a problem with January displaying an error message for 2007 data because there is no 2006 data in this database.
Some people who have been building PowerPivot tables may not realize that DAX supports several functions that can be used to trap errors and resolve display issues like the one above. For this week, I’m just going to focus on two DAX functions, IF() and ISBLANK(). The IF() function works much like you probably expect. It begins with a expression as the first parameter that evaluates to a Boolean value. If the expression evaluates to TRUE, the second parameter is evaluated and its result is returned from the function. If the expression is FALSE, the third parameter is evaluated and returns its result from the function. In this case, the expression I want to evaluate is whether StoreSales is blank or if LastPeriodSales is blank. Either or both of these measures could be blank if there were no data records for the current month or previous month respectively. Note that this is not the same as records having a value of zero.
Since I want to return a value of ‘0’ if either of these two measures are blank, I chose to use the expression:
ISBLANK([StoreSales]) || ISBLANK([LastPeriodSales])
This expression checks if the measure StoreSales is blank OR the measure LastPeriodSales is blank. Had I used the connector && rather than ||, I would be asking if the StoreSales measure and the LastPeriodSales measure were both blank. Since I only need to know that one or the other is blank, I use the ‘OR’ connector: ||.
Putting this all together, I get the expression:
SalesGrowth:=IF(ISBLANK([StoreSales]) || ISBLANK([LastPeriodSales]), 0, ([StoreSales] – [LastPeriodSales])/[LastPeriodSales])
Perhaps displaying a zero where either of these values is not defined is not exactly what I want. Afterall, a growth rate of 0% means something entirely different than an undefined value. Rather, I want to display a blank in the field if the value for StoreSales or Last Period Sales does not exist rather than a value of ‘0’. This small change has the added benefit of removing the status icon from the final column when either sales value is not defined. To return a blank, use the BLANK() function as the True argument in the IF expression.
Refreshing my Excel pivot table one last time I now have a properly ordered and formatted table as shown below.
C’ya next time as I explore more interesting technology features in Microsoft’s current crop of products.