This post will show you how to show items with no data from tabular model using an Excel Pivot Table. The reason for this post stems from the below picture.
As you can see in a tabular model the option to display these items is grayed out. You may find that consumers of the reports you’re building want to see the zero values when they are using pivot tables connected to your power pivot model. There are two methods to solve this problem. The first is fairly simple. You wrap all of your measures in an ISBLANK statement in DAX. Here is an example below.
Sum of TotalDue:=IF(ISBLANK(SUM(SalesOrderHeader[TotalDue])),0,SUM(SalesOrderHeader[TotalDue]))
As you can see, if the measure evaluates to blank it instead return a zero value, but if the measure does have a value, we instead return the whole measure. Using Adventure Works we can see how this works below.
The Slicer is filtering the pivot table but the pivot tables are still returning zero values for all of the values. This keeps the formatting of the pivot table and doesn’t hide any of the rows. This method will work for most of the slicers that you use, where it doesn’t work is when you have a slicer that is also a row value.
As you can below picture the slicer is filtering on country name and the pivot table row values are also country name. This example is using the same measure as above, but it doesn’t return the zero values as expected.
What if the consumer of the report has asked for the formatting of the pivot table to stay the same or they do want to see zero values? There is one more trick to get these values to display. Please note before doing this next step this will slow down performance of the PowerPivot model.
Step 1. Create a new table in the PowerPivot model and bring in the values that you want to slice by and the primary key. In the example below I have created a Territory Slicer table, which contains the primary key and the name of the territories.
Step 2. Create a relationship between the new table and the table that you want to slice. Important note here the relationship must be the same as shown in the picture, the table that you want to slice, must point to the slicer table.
Note you may have to refresh the data source after this step.
Step 3. Create a slicer using the slicer table that you just made.
That is it, your slicer should now be functioning as you need it to. See the below picture
These two methods are ways to get around the limitations of tabular model and should help in keeping formatting of pivot tables the same.
Posted by admin