How to create a single slicer that can combine data from multiple columns?

[ksheth]

Hello,

I have the below sample data:

Person	Bananas	Apples	Pears	Grapes
A	Yes	No	Yes	Yes
B	Yes	No	No	Yes
C	No	Yes	No	No
D	Yes	Yes	Yes	Yes
E	No	Yes	Yes	No
F	No	No	No	Yes

I want to create a slicer that has the following four options: 1) Bananas, 2) Apples, 3) Pears, and 4) Grapes, and each option should then filter to ALL individuals that said "Yes" to that option. I want these options all on the same slicer.

So for example, if I select "Apples" in the slicer, I want all of visualizations to update to include Persons C, D, and E. Or, if I select "Grapes", all visualizations should update to include Persons A, B, D, and F. Note how Person D shows up for both Apples and Grapes, as reflected in the table. I also would like to be able to multi-select more than one option at the same time, but this part is not necessary if it can't be done.

I have tried using the SWITCH formula in a calculated column and using that column for the slicer, but I noticed that each line overwrites the previous ones. So in the example of Person D, they would only show up if "Grapes" is selected but not for "Bananas", "Apples", or "Pears" like they should per the table. I instead want Person D to show up for all options that were marked "Yes" in the table.

Fruit = 
SWITCH(
    TRUE(),
    [Bananas] = "Yes", "Bananas",
    [Apples] = "Yes", "Apples",
    [Pears] = "Yes", "Pears",
    [Grapes] = "Yes", "Grapes"
)

How can I achieve this?

[v-venuppu · Accepted solution]

Hi @ksheth ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @FBergamaschi for the prompt response.

I have developed a PBIX file using the given sample data.Please go through the attached PBIX file for your reference.

[raja1992]

I’ve had to do this before 🙂. The trick is to unpivot the fruit columns so they become rows:

1. In Power Query, select the columns Bananas, Apples, Pears, Grapes.

2. Right-click → Unpivot Columns.

3. You’ll now have a table like:

Person   Fruit    Value
A        Bananas  Yes
A        Apples   No
A        Pears    Yes
A        Grapes   Yes

1. Load this back → use the new Fruit column in your slicer.

2. Add a visual/filter where Value = "Yes" so only the “Yes” responses count.

Now when you pick “Apples” in the slicer, Persons C, D, and E show up. Multi-select works too — if you pick Apples + Grapes, you’ll get everyone who said Yes to either of those fruits.

👉 Tip: If you can’t change the model in Power Query, you could do something similar with UNION in DAX to reshape the data into a long table. But Power Query unpivot is the cleanest.

[raja1992 · Accepted solution]

I’ve had to do this before 🙂. The trick is to unpivot the fruit columns so they become rows:

1. In Power Query, select the columns Bananas, Apples, Pears, Grapes.

2. Right-click → Unpivot Columns.

3. You’ll now have a table like:

Person   Fruit    Value
A        Bananas  Yes
A        Apples   No
A        Pears    Yes
A        Grapes   Yes

1. Load this back → use the new Fruit column in your slicer.

2. Add a visual/filter where Value = "Yes" so only the “Yes” responses count.

Now when you pick “Apples” in the slicer, Persons C, D, and E show up. Multi-select works too — if you pick Apples + Grapes, you’ll get everyone who said Yes to either of those fruits.

👉 Tip: If you can’t change the model in Power Query, you could do something similar with UNION in DAX to reshape the data into a long table. But Power Query unpivot is the cleanest.

[ksheth]

Hi @raja1992,

Thank you very much for your detailed explanation. I was able to unpivot those columns and make my table in the format you (and other user responses) have described, and create the single slicer. Now, the problem I am facing is having this slicer affect my other visualizations. I am not able to attach .pbix files in this forum and I am not allowed by my company to use links to Google Drive, DropBox, Box, etc., so will try my best to describe here.

I currently have the "Fruit" table formatted as you have described in your comment. I also have another table "Customers" with demographics for each person, like below:

Person	Age	Sex	Race
A	25	Female	Asian
B	38	Female	Black
C	17	Male	Black
D	47	Male	White
E	25	Male	Hispanic
F	31	Female	Asian

I currently have visualizations set up to show count breakdowns separately by age, sex, and race. I am trying to make it so that when no Fruits are selected, all of the visualizations show total counts. Then when "Banana" is selected from the slicer, all of the visualizations should update to only reflect people who bought bananas.

I currently have created a single-to-many relationship from "Customers" to "Fruit" based on the column "Person". However, when I select "Banana" from my slicer, none of my visualizations change. Do you have any ideas what I am missing?

Thank you very much!

[raja1992]

I’ve had to do this before 🙂. The trick is to unpivot the fruit columns so they become rows:

1. In Power Query, select the columns Bananas, Apples, Pears, Grapes.

2. Right-click → Unpivot Columns.

3. You’ll now have a table like:

Person   Fruit    Value
A        Bananas  Yes
A        Apples   No
A        Pears    Yes
A        Grapes   Yes

1. Load this back → use the new Fruit column in your slicer.

2. Add a visual/filter where Value = "Yes" so only the “Yes” responses count.

Now when you pick “Apples” in the slicer, Persons C, D, and E show up. Multi-select works too — if you pick Apples + Grapes, you’ll get everyone who said Yes to either of those fruits.

👉 Tip: If you can’t change the model in Power Query, you could do something similar with UNION in DAX to reshape the data into a long table. But Power Query unpivot is the cleanest.

[v-venuppu · Accepted solution]

Hi @ksheth ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @FBergamaschi for the prompt response.

I have developed a PBIX file using the given sample data.Please go through the attached PBIX file for your reference.

[ksheth]

Hi @v-venuppu 

Thank you very much for your sample .pbix file. I was able to unpivot those columns and make my table in the format you (and other user responses) have described, and create the single slicer. Now, the problem I am facing is having this slicer affect my other visualizations. I am not able to attach .pbix files in this forum and I am not allowed by my company to use links to Google Drive, DropBox, Box, etc., so will try my best to describe here.

I currently have the "Fruit" table formatted as you had shared in the sample .pbix file. I also have another table "Customers" with demographics for each person, like below:

Person	Age	Sex	Race
A	25	Female	Asian
B	38	Female	Black
C	17	Male	Black
D	47	Male	White
E	25	Male	Hispanic
F	31	Female	Asian

I currently have visualizations set up to show count breakdowns separately by age, sex, and race. I am trying to make it so that when no Fruits are selected, all of the visualizations show total counts. Then when "Banana" is selected from the slicer, all of the visualizations should update to only reflect people who bought bananas.

I currently have created a single-to-many relationship from "Customers" to "Fruit" based on the column "Person". However, when I select "Banana" from my slicer, none of my visualizations change. Do you have any ideas what I am missing?

Thank you very much!

[DaleT]

Hi,

I think this is a modeling issue. Actually, the Fruit table is a fact table. Adding a new fruit-only table as a dimension table. All the slicers should be from dimensional tables ideally. Please refer to https://drive.google.com/file/d/16eHJZBWKk-ba07ggcB0_6PUYUgWHv3vD/view?usp=sharing , which is based on the file of @v-venuppu .

[ksheth]

Hi @DaleT,

Thank you for your response! I was able to solve my issue. I kept my relationship from the "Customers" table to the "Fruit" table based on "Person" as one-to-many, both changed the direction to "both" and that seems to have fixed the problem.

[Ashish_Mathur]

There seems to be some problem with the relationships (should be Many to One and Single).  To be able to help more, i will need the PBI file.

[ksheth]

Hi @Ashish_Mathur,

Thank you for your response! I was able to solve my issue. I kept my relationship from the "Customers" table to the "Fruit" table based on "Person" as one-to-many, both changed the direction to "both" and that seems to have fixed the problem.

[FBergamaschi]

You should first unpivot the table, such table is not ok for your purpose

get a table like

Person   Fruit    Yes/no

Then, please show an example of what your are trying to obtain

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI