Cross-Filtering in Slicers with Cube Formulas

Back from Paradise, Here’s a Quick Tip

Funny thing about vacations is that everyone is waiting on you when you get home.  But man, what a vacation.

Anyway, I’m juggling my final edits to Alchemy, spending two full days with a client, AND teaching a class on Wednesday/Thursday, so today’s post will be brief, but hopefully still useful:

Power Pivot Slicers and Cross Filtering with a Pivot

PivotTable with Two Slicers.  Gender Slicer “Cross Filters” the Customer Name Slicer,
As Expected.  All is Right with the World.

Now we convert the pivot to cube formulas:

Converting Pivot to Cube Formulas

And look what happens to the cross filtering:

Cross Filtering in Slicers is Not Working with Cube Formulas

 


The Answer?  R-E-S-P-E-C-T.  (Respect a pivot, that is).

Turns out, slicer cross-filtering is ONLY driven by pivots.  Cross-filtering doesn’t “respect” cube formulas at all.

So, let’s give the slicers something to respect!

Just slap a pivot in the sheet.  It only needs one measure, so pick something simple and fundamental, like [Orders]:

image

Single-Cell Pivot, Just One Measure

Then we select that pivot, and go to Slicer Connections:

image

image

Bring Up Slicer Connections Dialog, Check the Checkboxes
for the Slicers You Want Cross-Filtered

And boom!  The slicers are back to behaving:

image

No Men Allowed, Just Like Before.

And finally, you just hide that column of the worksheet, and no one needs to know you’ve got a pivot lurking in the shadows:

image

 

Voila!  Cross filtering works, and you can go about your business of making the report look precisely the way you want.  Which is the whole reason for using cube formulas in the first place, of course.

image

Cross Filtering Works, No Pivot is Visible, and I Do My Standard Trick
of Introducing “Spacer Bars” to Prove It’s not a Pivot Smile

3 Responses to Cross-Filtering in Slicers with Cube Formulas

  1. gregkramer1 says:

    Welcome back! Nice post.

  2. Jon Acampora says:

    Great post Rob! You can also move the pivot to another sheet. I’m referring to the pivot that the slicers are connected to. This is helpful when you want to publish the sheet that contains your formula based report. You can copy that sheet to a new workbook, replace the cubevalues formuals with values, then strip out the PowerPivot data model to slim down the file size. Of course I have a macro that automates this process, but not having the pivot table in the report sheet makes it easier to strip down the file for sharing.

    You will also lose the slicer functionality with this, so I might be getting off topic here.

    I’m curious to know how others go about sharing reports that don’t necessarily need the PowerPivot data model in them. There are many cases where the end user does not have or need the PowerPivot add-in installed, and SharePoint is not an option.

    Thanks again!

  3. Sebastian says:

    pivot tables “lurking in the shadows” are also helpfull to connect more than one slicer of the same type to a cube formula report.

    Example: You want to compare sales from 2 periods in a cube formula report. The user should be free to select the periods he wants to compare, so you need 2 period slicer.

    For this the first cube formula showing sales is linked to the period slicer from the original pivot that you have converted into formulas. Now create a new pivot and add a period slicer. Nothing else is needed in the pivot and you can hide the pivot later, all you need is the slicer. Now copy the cube formula in the next cell and change the period slicer to the other period slicer from the “lurking” pivot.

Leave a Comment or Question