Dependent slicers

Anything related to PowerPivot and DAX Formuale
Posts: 13
Joined: Tue Jun 16, 2015 12:34 pm

Dependent slicers

Postby serky » Sat Jun 06, 2020 2:41 pm

I just have a basic question regarding slicers in powerpivot.

I have one lookup table: continent, country, city, saleskey.
Saleskey is the unique identifier/link to the data table.

I have set up 3 slicers for 'continent', 'country' and 'city' from my lookup table. These work fine but I would like them to be smarter:
how do I set up the 'country' slicer so that it only shows countries relevant to the continent that was picked?


Posts: 1093
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Dependent slicers

Postby MattAllington » Sun Jun 07, 2020 7:54 am

If you are using PowerPivot for Excel, this should be automatic.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Posts: 13
Joined: Tue Jun 16, 2015 12:34 pm

Re: Dependent slicers

Postby serky » Mon Jun 08, 2020 10:49 am

No, it's not automatic. The slicer is filtered but only sort of.

When a 'continent' had been chosen, the country slicer still shows [b]all [/b]the countries in the data table. It only highlights those that are relevant to the chosen continent as a darker shade AND puts them at the top of the slicer; the other countries are [b]still[/b] available on the slicer and are shown as a lighter shade below the relevant countries for the continent.

If it's not relevant, I don't want it shown at all. This stops the user selecting items that do not make sense.

Is this doable with standard PowerPivot - maybe it just needs the datamodel to be re-arranged? Or do I need to do some smarts in excel with VBA etc.

Posts: 25
Joined: Mon Apr 06, 2015 6:02 am
Location: Auckland, New Zealand

Re: Dependent slicers

Postby ozeroth » Fri Jun 12, 2020 10:07 pm

Hi there,

It sounds like you just need to tweak the slicer settings.
For each slicer, right-click the slicer => Slicer Settings => Tick "Hide items with no data".

Since it sounds like the slicers all use columns from the same table, they will automatically cross-filter each other.

If the slicers used columns from different tables, this would also work provided that the slicers were connected to a PivotTable containing one or more measures, and the values on the slicers would be hidden if they resulted in blank measure values.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests