Creating One Slicer for Attributes Across Multiple Columns (Rows Can Have Multiple Attributes)

[JamesChurch]

Good afternoon,

I'm trying to put together a slicer for transactional data filtered/sliced by business attributes. These attributes are based across 10 or so columns and different vendors can have multiple attributes. The column would be "Y" if the attribute applies or null. I've been searching around and can't find any example quite like my use case. Hoping you guys could help me out. My data looks something like this but with more attributes:

Row/Index	Vendor	Sale Amount	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Attribute 5	Attribute 6
1	Vendor A	500	Y		Y		Y
2	Vendor B	1,000		Y			Y
3	Vendor C	375		Y
4	Vendor D	2,650	Y			Y		Y
5	Vendor A	50	Y		Y		Y

I'm looking to create a slicer that lists Attribute 1, Attribute 2, Attribute 3, ..., Attribute 6 wherein the user can select all to get everything, a single attribute, or a combination thereof. For instance, if the user selects attribute 5, I want to show the activity under Vendors A and B.

If the user selects all or multiple, I want any row with any of the selected attributes. For instance, if Attribute 2 and 5 are selected on the slicer, I want to show ANY vendor that has EITHER attribute 2 or 5 (or both). So this would pull activity under vendors A, B, and, C.

If anyone could walk me through how to set this up or provide a sample file, I would greatly appreciate it. I'm also flexibile in how the table/data model is set up. I can add additional support tables or change the attribute indicator instead of just "Y" for each to something unique to each attribute if that helps accomplish this. I'm new to PBI so would welcome advice on best practice as well. Thank you!

[PaulDBrown · Accepted solution]

The recommended way to go about this is to unpivot the Attribute columns. In Power Query, select the Row/index, Vendor and Sale columns, right click and select "Unpivot other columns". Then filter out the blank rows for "sale". 
You can then create  dimension tables for Vendor  & Attribute and use these fields for slicers, filters and in measures.

Things you need to be aware of:

1. The sales are repeated across attributes where y value = True. You will need to decide the business logic if you need to split the sales by attribute by vendor. In other words, Vendor A had sales for a value of 500 split across three attributes (of which we know nothing about the split between attributes, if that is relevant). With the transformation, vendor A now has sales of 500 for three separate attributes, so the sum of the sales by attribute (1,500) is not the same as the original row total (500). 

2. The row/index columns are no longer unique, so you might want to delete this column and add a new index column in Power Query.

[JamesChurch]

Exactly what I was looking for, Paul, thank you. I appreciate the gif as well. I was worried that I would be getting duplicative transactions in my totals/stats as I'm not splitting the value by attribute but looks I was able to do this as a separate table to use as the filter source. Thanks again!