cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SMad
New Member

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

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 LocationPrimary Account OwnerCustomer NameCustomer StatusCustomer FlagCustomer StateEnd MarketEnd Market SegmentProduct GroupProduct CategoryOur 2020 SalesCustomer 2020 total buy
Loc1Smith, JohnCust1ExistingOutsideAZMkt1Sgmt1Group1Cat1100100
Loc1Doe, JaneCust2ExistingOutsideAKMkt2Sgmt2Group1Cat2150300
Loc1Smith, JohnCust3ExistingOutsideCOMkt1Sgmt1Group1Cat3200400
Loc2Bob, BillyCust4ExistingOutsideWIMkt3Sgmt3Group2Cat4200500
Loc2Sock, JackCust5ExistingInsideNVMkt4Sgmt4Group2Cat5300300
Loc3Fish, MardyCust6ProspectOutsideFLMkt2Sgmt2Group3Cat60450
Loc4Isner, JohnCust7ExistingOutsideUTMkt1Sgmt1Group4Cat7100200
Loc4Isner, JohnCust8ExistingOutsideAZMkt3Sgmt5Group4Cat7150150
Loc4Holly, BuddyCust3ExistingOutsideCOMkt1Sgmt6Group4Cat8250300
1 ACCEPTED SOLUTION
halfglassdarkly
Resolver I
Resolver I

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 1SLICER 2
Primary LocationLoc1
Primary LocationLoc2
Primary LocationLoc3
Primary LocationLoc4
Primary Account OwnerSmith, John
Primary Account OwnerDoe, Jane
Primary Account OwnerBob, Billy
Primary Account OwnerSock, Jack
Primary Account OwnerFish, Mardy
Primary Account OwnerIsner, John
Primary Account OwnerHolly, 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.

View solution in original post

2 REPLIES 2
halfglassdarkly
Resolver I
Resolver I

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 1SLICER 2
Primary LocationLoc1
Primary LocationLoc2
Primary LocationLoc3
Primary LocationLoc4
Primary Account OwnerSmith, John
Primary Account OwnerDoe, Jane
Primary Account OwnerBob, Billy
Primary Account OwnerSock, Jack
Primary Account OwnerFish, Mardy
Primary Account OwnerIsner, John
Primary Account OwnerHolly, 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.

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!