One Slicer for multiple columns, with multiple items in some cells

[SEC]

Hello - I am trying to create a slicer that filters project data by staff name. The staff names 1) are listed in 3 different columns, and 2) there may be more than 1 name per field. 

This is part of my base table titled Projects Summary (there are many more columns with project details):

Project Name	Project Lead(s)	Project Member(s)	Project Team Lead(s)	Project Type	Start Date	End Date
Systems	Jack, Carol	Beth, Charles, Tim	Seth	Type 1	1/1/2023	2/1/2023
Marketing	Jill	Gary, Frank	Tim	Type 2	4/5/2022	6//3/2022
Hiring	Bob	Jill	Jack	Type 1	7/4/2023	9/1/2023

I would like to add a slicer that just shows staff names (1 time only) from across these 3 columns: 

1. Project Lead(s)
2. Project Member(s)
3. Project Team Lead(s)

So the slicer would look like this:

• Beth 
• Bob
• Carol
• Charles
• Frank
• Gary
• Jack
• Jill
• Seth
• Tim

This would enable me to filter the other data in the report by staff involved in the project, regardless of role/title. 

I am new to Power BI and have tried many options without success. When I try to create a slicer with the 3 columns, they are listed in a hierarchy, which I do not want. I also don't know how to make this work with more than 1 name per cell. There is no max on the number of names that could go in a cell (i.e., no max on the # of project leads), so I don't know how I would create separate columns to support 1 name/cell.

Thank you!

[parry2k]

@SEC unpivoting the data is the right way to go, maybe you need to revisit your other visuals and update those. Making a duplicate copy of the table is not recommended because if visuals on a page are done using both the table and cross-filtering between these visuals might not be possible.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[SEC]

That makes sense. Thanks again for your help!

[parry2k]

@SEC BTW here is teh Power Query code that can take care of it, and here is how the slicer will look like

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7BCsIwEER/JeQciEmr4rVCFcFTews9RAk2JGklySV/7y4l6mnfDOxjlKJDSdmERBm96adj5Kzj6iF1Js+QZh29SYyMNhBoB2jhjOVtiAAQXHC5kw2grDgxRe86OpPt8kKv9Si86FgY6aNeHBpsqB4J0PI9PiMeOG82RtHVxs3SrY+fC6f+zzjyts44fWdMHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Project Lead(s)" = _t, #"Project Member(s)" = _t, #"Project Team Lead(s)" = _t, #"Project Type" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Project Lead(s)", type text}, {"Project Member(s)", type text}, {"Project Team Lead(s)", type text}, {"Project Type", type text}, {"Start Date", type date}, {"End Date", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Name", "Project Type", "Start Date", "End Date"}, "Team Type", "Team Member"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Columns", {{"Team Member", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Team Member"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Team Member", Text.Trim, type text}})
in
    #"Trimmed Text"

Transform Data -> start a blank query -> click advanced editor and add above code, you can tweak it as you see fit.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[SEC]

@parry2k Thank you so much!!! This worked for me. The only issue I ran into is that once I transformed the data to make this work for the slicer, the other visuals I already had in my report no longer worked.

This may be a silly question (I'm a beginner), but would it be possible to have 2 sets of the same data in a report - 1 dataset that is transformed to meet this specific slicer need, and 1 dataset with the same data that is not transformed this way that I can use to create other report elements?

Or perhaps another solution?

Thanks!

[parry2k · Accepted solution]

@SEC this is a typical example to restructure your table and unpivot it. The best approach would be to unpivot the table and then split each team member's name into rows and from there it should be pretty straightforward.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!