Powerpviot- Dynamic Filtering using Slicers post Convert to Formulaes

NOTE: THis post only applies to PwerPivot 2013

I am 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 i want to explain how to deal with the limitations posed once we have converted to formula.

A classical example is consider a 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 belongs to specific category there is nothing speacial 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 loose 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 there data, in this case how do we replicate the same filtered view which we had working in the Pivot report.

1.

PivotUnfiltered Items

PivotUnfiltered Items

2. 

Filtered Item Pivot table

Filtered Item Pivot table

3.

pic4

So in essence Problem now becomes as

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 likes once you convert to Formula and even apply the Slicers, clearly seeing that we can see all product despite of applying filter via the slicers, so essentially we see all the Products despite of 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.

Converted_1

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 , which i am pretty sure could be written though i didnt try it as you know we all have to save time 🙂 and this would have required some research. I would post it once i 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 🙂

1. 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.

Converted_1

2. Replace the Convert to formula [product name with the formula as below Add a new column, with 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.

Converted_1

 

 

Once you follow all the steps you are ready and rocking with least time, let me know your comments on the same