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