### 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:

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:

And look what happens to the cross filtering:

### 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]:

Single-Cell Pivot, Just One Measure

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

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

And boom!  The slicers are back to behaving:

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:

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.

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

#### Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

## This Post Has 6 Comments

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

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

3. Parseval says:

Hello, sorry to write in this old post but I don’t know where to ask about my issue with slicers 🙁

Imagine in above scenario you filter by Andrea Adams (this value is not explicit in the pivot table, but it is impacting in its values). When you active that slicer, figures in pivot table change. What I need is a way to find what data is behind these new numbers. When I try to do it, it works if the active slicer is the one in the rows. Is there a way to get a table with just the value for Andrea? (or a set of active slicers)

If it is already in your book, please let me know the chapter. I bought it yesterday and I just in chapter 4.