Forum Discussion
Question About Slicer Search
Project Manager Teams |
Josh, Bassine, Lorraine |
Lorraine, Bassine, Josh |
Michelle, Francine, Kay |
I have a column with project manager names that are entered with a comma delimiter separating each name (table above).
I am trying to have users find project manager teams using the powerbi slicer. The issue is that names are not entered in the same order throughout the column but we are talking about the same team: For example, Josh, Bassine, Lorraine is the same team as Lorraine, Bassine, Josh
Currently, when I enter names in the slicer search box, the serach only returns a result if the 3 names are typed in the search box in the same order as they are entered in the column.
For example, if I type in the slicer search box Josh, Bassine, Lorraine. It will return a result
Project Managers |
Josh, Bassine, Lorraine |
However, if I switch the order of the names when I type them in the slicer search box. For example: Lorraine, Bassine, Josh or Bassine, Josh, Lorraine . Then, it will not return a result. However, I need a result to be returned because it is the same team.
I need a measure formula or another tool that returns all the occurences of a team even if the order I enter the names in the search box does match the order they are entrered in the column. Is this possible?
I am new to Powerbi so I thank you in advance for detailed answers.
Project Managers |
Josh, Bassine, Lorraine |
Hi Ibra9
You can create a custom column in the query editor using Text.Split. Here's the sample M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVRLT+MwEP4rVs8+xI77OgYoUqEh3e4K0UUcTBVUizSR0t1K/PudsTNjt0iwp0Yznz3+HtPn59E6y9RIju7thxS3vW13rq2lKN1uXzdNPXqRHqIBQrUUB8cIkgPkyh6Pvi7uuuMeflZd31uoEMgAaGt7C63fHeKe7MnVPbXH0P7x17WtFOvefViqT6AeDoTD4ZSg9hTaT8XjcrGBG7fFpsDrqwW1ZzzUX3E+cg7Nom2tvKrboaYyqL0xR3Fg4uKd+SpU7brYrKqfUtyUy1+bpRSLRxqq9JdMFaq1cvYgxUNnQamqcSdHfBXKFPBIiG+hNsr00GG1OliArB18lpb1UpMzu4Q3l10j0PQ/DFMzdiQ1RM0jO35nfKBGARcnuPbm4P70Topr2zfdkdooXZjH8y/mav2FZzpn/p55ECHhr40HoK6DsMgM1Ka+F7DCnFRlAaEpiy18r5cFAVBAervwTC4oTGNSz6KqZ5QnEQOl56kfvD1xefKMVjDZrcslzH3i/CuisPA2aqNkwerPm0qrkqN06QPk5RCUjsxI/EG7CILqebkH/WMUeczkW4dzH78a9EO1qDij4AgiOjAlwJz2xtt7tjUGRSS/fRuB9CSjODRDYMLj09wYnSic5Jfa+besjEn1ZR+i02YcV9vnO26NQdG6Ia6N/29ogQV1pxysJFkGBXsFFW0U0aBGuDXJmKE1zngxwp8PCfjyDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, ProjectManagers = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", type text}, {"ProjectManagers", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([ProjectManagers], ",")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "PM"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"PM", Text.Trim, type text}}) in #"Trimmed Text"Hi Ibra9
If you want to keep a single team column and don’t need advanced slicers, you can normalize the names:
In Power Query, add a Custom Column:
Text.Proper(
Text.Combine(
List.Sort(
List.Transform(Text.Split([Project Managers], ","), each Text.Trim(Text.Lower(_)))
),
", "
)
)So both Josh, Bassine, Lorraine and Lorraine, Bassine, Josh become:
Bassine, Josh, Lorraine.Then you can use that column in your slicer.
Thanks🌹✨
6 Replies
Hi Ibra9
You can create a custom column in the query editor using Text.Split. Here's the sample M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVRLT+MwEP4rVs8+xI77OgYoUqEh3e4K0UUcTBVUizSR0t1K/PudsTNjt0iwp0Yznz3+HtPn59E6y9RIju7thxS3vW13rq2lKN1uXzdNPXqRHqIBQrUUB8cIkgPkyh6Pvi7uuuMeflZd31uoEMgAaGt7C63fHeKe7MnVPbXH0P7x17WtFOvefViqT6AeDoTD4ZSg9hTaT8XjcrGBG7fFpsDrqwW1ZzzUX3E+cg7Nom2tvKrboaYyqL0xR3Fg4uKd+SpU7brYrKqfUtyUy1+bpRSLRxqq9JdMFaq1cvYgxUNnQamqcSdHfBXKFPBIiG+hNsr00GG1OliArB18lpb1UpMzu4Q3l10j0PQ/DFMzdiQ1RM0jO35nfKBGARcnuPbm4P70Topr2zfdkdooXZjH8y/mav2FZzpn/p55ECHhr40HoK6DsMgM1Ka+F7DCnFRlAaEpiy18r5cFAVBAervwTC4oTGNSz6KqZ5QnEQOl56kfvD1xefKMVjDZrcslzH3i/CuisPA2aqNkwerPm0qrkqN06QPk5RCUjsxI/EG7CILqebkH/WMUeczkW4dzH78a9EO1qDij4AgiOjAlwJz2xtt7tjUGRSS/fRuB9CSjODRDYMLj09wYnSic5Jfa+besjEn1ZR+i02YcV9vnO26NQdG6Ia6N/29ogQV1pxysJFkGBXsFFW0U0aBGuDXJmKE1zngxwp8PCfjyDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, ProjectManagers = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", type text}, {"ProjectManagers", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([ProjectManagers], ",")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "PM"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"PM", Text.Trim, type text}}) in #"Trimmed Text"Thank you
Hi Ibra9
If you want to keep a single team column and don’t need advanced slicers, you can normalize the names:
In Power Query, add a Custom Column:
Text.Proper(
Text.Combine(
List.Sort(
List.Transform(Text.Split([Project Managers], ","), each Text.Trim(Text.Lower(_)))
),
", "
)
)So both Josh, Bassine, Lorraine and Lorraine, Bassine, Josh become:
Bassine, Josh, Lorraine.Then you can use that column in your slicer.
Thanks🌹✨
Thank you
Hi Ibra9
Please check if this solution helps you:
First, you create a Managers table using Power Query.
Then, you create a measure "SelectedMgmtFilter" for the selection.
Finally, add this measure as a filter to your visual. Once you pick managers from the slicer, the table will be filtered accordingly.
I have attached the solution I suggest.
Thank you. It worked as well
