Forum Discussion
Create Slicer for Multiple Columns
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 |
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.
5 Replies
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
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).
