Create a slicer using multiple columns of a table - NOT NESTED

[Loubot3000]

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?

[nikhilbordekar]

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")

    )

)

[nikhilbordekar]

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.

[lucadelicio]

PLEASE MARK IT AS A SOLUTION TO HELP OTHER PEOPLE

[jitendracs2004]

I tried to find the option "Mark as a solution" but didn't find 🙃 otherwise would have done that. 

[lucadelicio]

😪

[Loubot3000]

Hey sorry when I have time to check your solution I shall mark it if it works for what I need.

[jitendracs2004]

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

[jitendracs2004]

Thanks.

https://we.tl/t-jufs4v4x5A

I hope you will get the file

[lucadelicio]

Hi ji
here the link of the pbix
https://we.tl/t-6Xtz2nj6sQ
You have to manage two table that corrispond to two parameter.
They manage you column.
The first parameter "Columns ever show" is the first column of your table and are show indipendent from the choose of your slicer FE.
The secondo parameter "Column Parameter" show the column based on the FE slicer.
A filter of a measure is apply on the table to select only the rows you want.
So the combination of those things returns your result.
Not so simply to do, but i did it!

I hope I have been helpful.
Mark it as a solution if resolve your problem.

Ciao!

[ljx0648]

Hi Luca,

By any chance you still have the PBI file for the solution of this matter? 

I have a similar question and would much appreicated your help!

I tried to click the link to download the PBI in the link you provided but its expired.

Thank you!

[jitendracs2004]

@lucadelicio You are genious. Never thought of this before... Ealier i had to show everything in case no Features are selected and filter only when something gets selected. But i will try to ammend the requirenet to have atleast one selected.

Thanks a ton 

[lucadelicio]

thank you for your compliments.
With two parameter combinated i had never try, but it goes good! 😉
PowerBI is a brain teaser 😄
PLEASE MARK IT AS A SOLUTION TO HELP OTHER PEOPLE

[lucadelicio]

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-mVLwIdW6ZI

I 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]

Hello @Loubot3000 ,

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 👍

Follow me on Linkedin

[Loubot3000]

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

[jitendracs2004]

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

[lucadelicio]

Can you send me the pbix or the excel wirth the data?

I try to look at this.

Thank you