Power Pivot: Dynamic Filtering Using Slicers Post Convert to Formulas

NOTE: This post only applies to Power Pivot 2013

We are not going to explain why do we need to use the Convert to Formula functionality in Power Pivot and what the advt it provides that a topic for a separate discussion in itself, in this post we want to explain how to deal with the limitations posed once we have converted to formula.

A classical example is considered an Active Pivot report where you have data based on slicer things like Product category(Slicer) and Product and Unit sale Price(data) on the report now if the end users wants to play with reports and wants to see only those products which belong to specific category there is nothing special which needs to be done it comes out of box Screen print 1 and Screen Print2 shows the same, but what happens when we convert the report using the OLAP tools we seem to lose the functionality as now the data is referred as key wise so all the product names have to hard-coded if we want to see their data, in this case how do we replicate the same filtered view which we had working in the Pivot report.

1.

http://bishtabhinav.files.wordpress.com/2014/01/pic12.gif?w=300&h=263

PivotUnfiltered Items

2. 

http://bishtabhinav.files.wordpress.com/2014/01/pic2.gif?w=300&h=250

Filtered Item Pivot table

3.

http://bishtabhinav.files.wordpress.com/2014/01/pic4.gif?w=300&h=148

So in essence the Problem now becomes this:

Problem Statement:  Once a report is converted to formulas the Slicers would suddenly stop working,  how to replicate the dynamic behavior of report with changes in slicers changing the data.

This is what report looks like once you convert to Formula and even apply the Slicers, clearly seeing that we can see all product despite applying filter via the slicers, so essentially we see all the Products despite slicers for product subcategory, in the screen print I have selected BikeRacks and should see only Bike Racks products but that’s not happening.

 

4.

http://bishtabhinav.files.wordpress.com/2014/01/converted_1.gif?w=300&h=258

Now to make the slicers work again and see only relevant data, one has 2 ways:

a) Write an adaptive complex DAX formula to hide the products, written though we didn't try it as you know we all have to save time and this would have required some research. We would post it once we have got some time to spare.

b) Follow the method am going to depict, this method is essentially a cheat and reads from the hidden columns http://s0.wp.com/wp-includes/images/smilies/icon_smile.gif?m=1129645325g

Add a new active Pivot which too is connected to the same slicer, with the columns you need (We are going to hide this pivot)

5.

http://bishtabhinav.files.wordpress.com/2014/01/converted_12.gif?w=300&h=168

2. Replace the Convert to formula [product name with the formula as below Add a new column, with a formula like:

=IFNA(CUBEMEMBER("ThisdataModel","[Product].[Name].”&”[" &L7  &"]“),”")

Copy and extend the same formulas for all rows, to explain the formula IFNA checks for errors and would replace them with blanks, &M7& is the cheat where we are reading the data from the active pivot which are always filtered see screenprint 5

 

Where M would refer to the active pivot column, ensure that the new pivot which was inserted had these properties unchecked, such that the slicer is able to check data properly

6.

http://bishtabhinav.files.wordpress.com/2014/01/converted_11.gif?w=300&h=233

 

 

Once you follow all the steps you are ready and rocking. Leave any comments below.