Forum Discussion
Create a slicer using multiple columns of a table - NOT NESTED
I want to create slicers that list categories representing multiple different columns in my dataset in a non-nested way.
For example, I have in my central demographics table a bunch of columns representing subscription status to various different services, and the rows are each unique respondent (with a UUID column giving them unique keys). The values of the subscription columns are Yes/No. So for example a snippet of this demographics table would look something like:
| UUID | Netflix subscriber | Amazon Prime Subscriber | Hulu Subscriber |
| frbshkgasirfgs | Yes | No | Yes |
| feisahgjkarfa | No | Yes | Yes |
| efhvnakinhak | No | No | Yes |
(Though obviously realistically there would be way more columns for other data that visuals are built off of, and thousands of rows, and other unpivoted tables that are related to these tables via multi way many-to-one relationships via UUID.)
What I want is a slicer with a drop down menu that would be like:
| Subscription \/ |
Netflix [] |
Amazon Prime [] |
Hulu [] |
Where that first row is the slicer name, \/ is the drop down button, and [] are the tick boxes
By ticking, you're filtering that page to just people answering "Yes" (I image you'd just apply a bunch of "Yes" filters to the slicer in the filter pane).
From what I've tried, adding multiple fields to a slicer makes them nested.
Having those columns as a seperate unpivoted table, with a both way many-to-one relationship via UUID to the central table, and adding the unpivoted column filtered by "Yes" response seems to not work either.
Any ideas?
20 Replies
lucadelicioImpactful Individual
Hi,
try to go into the power query transform data mode:
- REFERENCE the table
- Unpivot all the columns
- Remove other columns
- Distinct value
Then put the filed into the slicer.
In this way you have the slicer connected to the column of your starting table.
Here the pbix link:
https://we.tl/t-mVLwIdW6ZII hope I have been helpful.
Let me know if resolve your problem.
It's more easier to help you if you upload your pbix.
Mark it as a solution if resolve your problem.Ciao!
- Idrissshatila
Super User
Hello Anonymous ,
You could acheive this using field parameters.
https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Bro thank you 🙏 been trying to do this for ages. Crazy you can't just do it in normal slicers.
It still doesn't affect anything though because it's just selecting that column overall - but everybody has some response for that column. I just want it to select "Yes" values. If I add the relevant fields to the filter pane of that field paramenter slicer, and have them only select "Yes", it doesn't affect anything when I select that category in the field parameter slicer.
Any idea how to achieve the behaviour I want?
Thanks
Hi Loubot,
I have the exact request but unable to find any soultion. Did you get anything on this. Please share in case you cracked it.
Thanks,
Jit
Thanks for your help. I have prepared the data in excel. What i have is 5 Dimensions,
Enterprise service FE1 FE2 FE3 and 3 measures
MoU NoUC NoSC I want to have a slicers with Enterprise, service and Features (FE1, FE2 and FE3 as values) respectively. If some one select FE1 or FE2 or FE3, only Yes of respective columns get filtered out. User can have multi select option enabled.
Not able to attach the link, so sending the data here itself..
Date Enterprise service FE1 FE2 FE3 MoU NoUC NoSC 20230601 Eri000000_1 IPTF Yes No No 10 1 50 20230602 Eri000000_2 DTF No Yes No 12 1 60 20230603 Eri000000_3 DTF Yes No Yes 20 0 10 20230604 Eri000000_4 DTF No No No 21 0 20 20230605 Eri000000_5 DTF No Yes No 16 0 30 20230606 Eri000000_1 DTF Yes No Yes 18 26 12 20230607 Eri000000_2 DTF Yes No No 29 10 14 20230608 Eri000000_3 DTF No Yes No 30 12 15 20230609 Eri000000_4 IPTF Yes No Yes 25 20 10 20230610 Eri000000_5 IPTF Yes No No 16 21 20 20230601 Eri000000_3 IPTF No Yes No 18 16 30 20230602 Eri000000_4 IPTF Yes No Yes 29 18 12 20230603 Eri000000_5 IPTF Yes No No 30 29 14 20230604 Eri000000_1 DTF No Yes No 25 30 15 20230605 Eri000000_2 DTF Yes No Yes 10 25 50 20230606 Eri000000_3 DTF Yes No No 12 16 60 20230607 Eri000000_1 IPTF No Yes No 20 18 10 20230608 Eri000000_2 IPTF Yes No Yes 21 10 20 20230609 Eri000000_3 IPTF Yes No No 16 12 30 20230610 Eri000000_4 IPTF No No No 18 20 12 20230601 Eri000000_5 IPTF Yes No Yes 29 21 30 20230602 Eri000000_1 IPTF Yes Yes Yes 30 16 12 20230603 Eri000000_2 IPTF No Yes No 25 18 14 20230604 Eri000000_3 IPTF Yes No Yes 18 29 15 20230605 Eri000000_4 IPTF Yes No No 29 30 10 20230606 Eri000000_5 DTF No Yes No 30 25 20 20230607 Eri000000_3 DTF Yes No Yes 25 18 30 20230608 Eri000000_4 IPTF Yes No No 16 29 12 20230609 Eri000000_5 IPTF No Yes No 18 30 14 20230610 Eri000000_1 IPTF Yes No Yes 29 0 15 - lucadelicio
Upload it on wetransfer it is free.
https://wetransfer.com/
Thank you
- lucadelicio
PLEASE MARK IT AS A SOLUTION TO HELP OTHER PEOPLE
I tried to find the option "Mark as a solution" but didn't find 🙃 otherwise would have done that.
- lucadelicio
😪
Even I am facing similar problem, i want TRx(all rows), NRx and NBRx in one filter nut all three may or may not be overallping. I tried to download the solution .pbix file from wetransfer link but unfortunately transfer has expired and is not available any more. It will be great if anyone can help here. lucadelicio
- lucadelicio
Hi nikhilbordekar as attachment excel data and pbix.
You have to create a single column to filter your rows for any combination.
Then put the Filter Column in the slicer.Filter Column =IF(ISBLANK(Tabella1[NRx]) && ISBLANK(Tabella1[NBRx]), "TRx",IF(ISBLANK(Tabella1[NRx]), "TRx-NBRx",IF(ISBLANK(Tabella1[NBRx]), "TRx-NRx", "TRx-NBRx-NRx")))I just want 3 filters TRx, NRx and NBRx, if I select TRx means all rows should come irrespective of what is there in the rest of the columns. If i select NRx all rows should come where NRx is there irrespective of what are there in rest of the columns. and same for NBRx. can you suggest me a formula for this as I am new to this Power BI tool. One more doubt have you created new column calculation for this or new measure? or anything else. This information will be really helpful.
