Using two slicers: one to select the column to use to filter, second showing values within column

[SMad]

I am brand new to PowerBI, so apologies if I call things by the wrong name! I have done significant searching for a solution to this but haven't found anything.

I have data that shows customer total buy and my company's sales for different products along with other information. I'd like to be able to visualize total buy or sales with a stacked column chart, but then be able to do several different kinds of filters.

There are 10 columns that I'd like to be able to use to filter. These include columns like "Product Group", "End Market", "Customer State", "Primary Account Owner", etc. 

The ideal output I'm looking for is having one slicer to select one of the 10 columns that I'd like to filter on and then a second slicer showing the options from that column. 

For example, the first slicer should show all 10 column names as mentioned above, and when "Product Group" is selected, the second slicer would populate (or filter down to?) showing the four potential product groups: Group1, Group2, Group3, Group4. 

The actual visualization would only change when this second slicer selection is made. Below is sample data, but I don't have an example PBI file.

I've seen mention of unpivoting data, or using dynamic heirarchy, but I'm not familiar with these and not sure what would work best in this case.  Thanks!

Primary Location	Primary Account Owner	Customer Name	Customer Status	Customer Flag	Customer State	End Market	End Market Segment	Product Group	Product Category	Our 2020 Sales	Customer 2020 total buy
Loc1	Smith, John	Cust1	Existing	Outside	AZ	Mkt1	Sgmt1	Group1	Cat1	100	100
Loc1	Doe, Jane	Cust2	Existing	Outside	AK	Mkt2	Sgmt2	Group1	Cat2	150	300
Loc1	Smith, John	Cust3	Existing	Outside	CO	Mkt1	Sgmt1	Group1	Cat3	200	400
Loc2	Bob, Billy	Cust4	Existing	Outside	WI	Mkt3	Sgmt3	Group2	Cat4	200	500
Loc2	Sock, Jack	Cust5	Existing	Inside	NV	Mkt4	Sgmt4	Group2	Cat5	300	300
Loc3	Fish, Mardy	Cust6	Prospect	Outside	FL	Mkt2	Sgmt2	Group3	Cat6	0	450
Loc4	Isner, John	Cust7	Existing	Outside	UT	Mkt1	Sgmt1	Group4	Cat7	100	200
Loc4	Isner, John	Cust8	Existing	Outside	AZ	Mkt3	Sgmt5	Group4	Cat7	150	150
Loc4	Holly, Buddy	Cust3	Existing	Outside	CO	Mkt1	Sgmt6	Group4	Cat8	250	300

[halfglassdarkly · Accepted solution]

Hi @SMad I don't think there is an efficient way of doing this using just slicers - in order to make it work you could to create a table with all the values from each of your 10 columns appended together and grouped by your column names e.g. (for the first two columns only):

SLICER 1	SLICER 2
Primary Location	Loc1
Primary Location	Loc2
Primary Location	Loc3
Primary Location	Loc4
Primary Account Owner	Smith, John
Primary Account Owner	Doe, Jane
Primary Account Owner	Bob, Billy
Primary Account Owner	Sock, Jack
Primary Account Owner	Fish, Mardy
Primary Account Owner	Isner, John
Primary Account Owner	Holly, Buddy

You could either do that in DAX with a calculated table or in Power Query (using append), but I don't know that this is is a good approach. For one thing you would only be able to filter on one attribute in slicer 1 at a time, which fits what you've asked for, but might not be desirable for end users? Your table relationships would get really complicated because the relevant foreign key in your fact table would change depending on your selection in slicer 1 and you would have to create measures to define which relationship to use based on your first slicer selection. You may also run into issues with scalability - it's achievable with your demo data but as your dataset grows it's going to get less efficient.

The other way you could achieve this would be using bookmarks, which would let you create different views to show or hide individual slicers as required. A bookmark can affect all visuals/slicers or just those you specify, and you can set a bookmark to affect the filtering in a slicer, or only it's visibility, which makes it quite a flexible option.

I don't believe there is a way to trigger a bookmark with a slicer though, so your first 'slicer' would need to be a set of buttons that trigger switching between different bookmarked views to show or hide each of your slicers for different objects.

If that all sounds fiddly, it is. It's pretty easy to implement something like this for a few columns, but if you're trying to allow for users to filter any of the 10 columns it might be better to stick to utilising out of the box slicer functionality with different slicers filtering each of your columns. If you are concerned about running out of screen real estate, one option is to implement a toggleable slicer pane using bookmarks to show and hide all of your filters at once. That's also a bit fiddly to set up but less so than controlling the visibility of each slicer individually.

In terms of hierarchies, if you have a natural hierarchy between your columns, e.g. End Market and End Market segment you can just drag multiple columns into your slicer and arrange them from top to bottom from least to most granular and set the slicer view to drop down, and that will let you drill through the hierarchy and filter at any level.

Not a solution I know, but hope that helps in terms of providing context.

[SMad]

@halfglassdarkly Thank you for your detailed and thoughtful response! I think using bookmarks and a panel of slicers is a great solution. You're right that only allowing users to filter on one column is not exactly what users want, but I was worried about the screen real estate. I appreciate you taking the time to explain all that to me.