Dependent slicers

Anything related to PowerPivot and DAX Formuale
serky
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?

Thanks

MattAllington
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

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

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

Regards
Owen


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests

cron