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

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

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

    Thanks in advance

    • Chris Gilbert says:

      One way to get that information (if just double-clicking on a cell in your PowerPivot Table doesn’t provide you with a drill-down screen – some tables have formulas that don’t play well with the drill-down feature) would be to go back into the PowerPivot [green-bars] window, and then duplicate the filtering that you have out in Excel.

      When you’re inside the PowerPivot User Interface, you filtering is only for display – it doesn’t affect what’s out at the Excel level. I use this for debugging my measure formulas, too; since any filters that you apply on the GUI data columns are reflected in the Measure window (bottom of your screen) results.

Leave a Comment or Question