Create Slicer for Multiple Columns

[ss8551]

Hi,

I have a PBI that has multiple columns and I'm looking for a way to create a slicer that filters based on the values of these columns.  The Rep could appear in multiple columns but i need to be able to filter based on if the rep took any of these courses.

Example:
rep cd456 took two courses the intro course and an acknowledgement course i need to be able to have a slicer that can filter based on any of the these values to show the data for these reps).  so in other words i want to filter for reps that took course 5764709  Intro, or course 5999904, etc.   it is possible that the rep could have taken multiple courses so in the case of rep cd456 selecting either course 5764709 Intro or course 5925110 Acknowledgement the reps data will show.  there will be instances where a rep took no course or the course was not taken by any reps (at this time).

Rep	5764709 Intro	5999904 Acknowledgement	5342762 Practice	6811954 Intro	5925110 Acknowledgement	6809358 Intro	5395326 Intro	6614325 Acknowledgement	6771484 Practice
ab234			5342762 Practice
cd456	5764709 Intro				5925110 Acknowledgement
ef678	5764709 Intro							6614325 Acknowledgement	6771484 Practice
fg901
gh123				6811954 Intro
ij456						6809358 Intro		6614325 Acknowledgement
jk789								6614325 Acknowledgement
lm012
mn345									6771484 Practice

[ss8551]

Thank you for your reply.  I was going to try to Unpivot the columns, however, the fields that I'm working with are calcualted columns and therefore I'm unable to Unpivot these columns.  I apologize that i negelected to mention this in the original post.  I had to bring the data into this table from another Power BI (Direct Connection) and therefore I needed to create calculated columns for this data.

Any thoughts on how this can be accomplished with calculated columns?  I attempted to Concanate the columns however, since it's possible that a rep could have completed more than one course this does not allow one occurance of each course (some may have two or three courses).

[ss8551]

sorry some reps may show 2 or 3 courses and therefore concatenation will not provide a unique value for each course.

[ss8551]

Any thoughts since this cannot be unpivoted how to accomplish this?

[ss8551 · Accepted solution]

If anyone can find a way to resolve this so that the traditional slicer works with multiple calculated columns it would be appreciated.  In the meanwhile i found another visualization as a work around.  not as good a the traditional slicer but it does seem to work for now.  It is the selection slicer by Walnut Innovation.

[v-yiruan-msft]

Hi @ss8551 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Unpivot these course columns in Power Query Editor as below screenshot

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZA7D4MwDIT/CsrMECd2HmPHbt0RA4VAeQUJIfXvVxQJaHlJt/nu89lRxJKnkMhCNokkCq1E8OiTdChTNw8WxWHE0gxJjXatUHMb3P3Qd2sXWUEAPLilte/ejcsK1zo/bEAuV9qcgWYpBSgF7RCV1oAGl84jOC8sh13QanvxAiF/lhgAS7hfY0yU1XT4fznDrSSzzh33/YKqWht7VO4i27QcxNVtrZdIJ6bt1+IP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rep = _t, #"5764709 Intro" = _t, #"5999904 Acknowledgement" = _t, #"5342762 Practice" = _t, #"6811954 Intro" = _t, #"5925110 Acknowledgement" = _t, #"6809358 Intro" = _t, #"5395326 Intro" = _t, #"6614325 Acknowledgement" = _t, #"6771484 Practice" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rep", type text}, {"5764709 Intro", type text}, {"5999904 Acknowledgement", type text}, {"5342762 Practice", type text}, {"6811954 Intro", type text}, {"5925110 Acknowledgement", type text}, {"6809358 Intro", type text}, {"5395326 Intro", type text}, {"6614325 Acknowledgement", type text}, {"6771484 Practice", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"5764709 Intro", "5999904 Acknowledgement", "5342762 Practice", "6811954 Intro", "5925110 Acknowledgement", "6809358 Intro", "5395326 Intro", "6614325 Acknowledgement", "6771484 Practice"}, "Course", "IsTake"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([IsTake] <> ""))
in
    #"Filtered Rows"

2. Create a slicer using the field [Course] and a table visual as below screenshot

If the above one can't help you get the expected result, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards