Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
fionamann_
Frequent Visitor

Combine columns in to one slicer

Hi,

 

I have a table in the folowing format (in reality there are many more rows and columns with various account types):

 

RegionAgeAccount 1Account 2Account 3Account 4Customer count
London35-45YesNoYesNo13
North East55-65NoNoNoYes10
Midlands75+YesYesNoYes5

 

I'd like to combine all of the account columns in to one, so I can create a slicer with a dropdown for each account type. This would then let me select total customer who have 'account 1' OR 'account 2'.

 

I should also mention the data is aggregated, hence the customer count. The only solution I have found so far is to:

1. Open Power Query

2. Add an index to the table

3. Reference the table and keep the relevant account(s), index and customer count columns

4. Unpivot the account columns

5. Load in to the main view, connect the new table to the base table and then create a slicer from here

 

The main draw back with this method is the file becomes huge. I wonder if there is any other solution? 

 

I'd also like to know if there's a clean way to allow people to select customers who have 'account 1' AND 'account 2'. Currently I just have seperate 'Yes' or 'No' slicers for every account type.

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @fionamann_ 
The solution that you found with unpivot is a best practice. 
Because the table for analysis should be "vertical" not only to use desired slicers.

Please refer to the first sections of the linked article :

https://zebrabi.com/guide/how-to-unpivot-data-in-power-bi/

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

3 REPLIES 3
Ritaf1983
Super User
Super User

Hi @fionamann_ 
The solution that you found with unpivot is a best practice. 
Because the table for analysis should be "vertical" not only to use desired slicers.

Please refer to the first sections of the linked article :

https://zebrabi.com/guide/how-to-unpivot-data-in-power-bi/

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

@Ritaf1983 

 

I've seen articles on that Zebra BI site that are AI generated and just plain wrong.  I'd be very hesitant sending anyone there to 'learn' from what may be incorrect information.

 

The articles are also overly long and waffly, just text, no helpful images, no video.  People would be better off searching for a video or other source that is better.

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi @PhilipTreacy 
I didn't suggest going to the Zebra BI site and learning from them in a general way. Instead, I provided a brief response along with a specific reference link. I believe these two paragraphs effectively summarize the issue. If you have a better alternative, please suggest it. I don't see any advantage for the post owner in your criticizing my answer.

 

Ritaf1983_0-1700035091825.png

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.