cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wpf_
Post Prodigy
Post Prodigy

How can I slice with a field that can represent two values?

So I have a table called 'managers', and a field named 'worklocation'.  Worklocation has a value of 'Location1', 'Location2', Location3', 'Location4', 'North', and 'South'.  For the managers that has worklocation 'North', they cover 'Location1' and 'Location2'.  The managers that has worklocation 'South', they cover 'Location3', and 'Location4'.  I want to use a slicer using 'Location1' - 'Location4' only, and if 'Location1' is selected, it shows all the managers in Location1 and all the Manager's in 'North'.  If Location2 is selected it shows all managers in Location2 and managers in North.  If Location3 is selected it shows all the managers in Location3 and all the managers in South.  If Location4 is selected it shows all the managers in Location4 and all the managers in South.  Hope someone can help. Thanks.

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @wpf_ ,

 

According to your requirements, it can be achieved by creating a measure or calculated column. But need to provide test data (delete sensitive information) and expected results. I will answer for you as soon as possible.


Let me know immediately, looking forward to your reply.

Best Regards,
Henry

sayaliredij
Solution Supplier
Solution Supplier

I have used some test data  

 

But you can below power query

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcrJT04syczPM1SK1YlWMgKK+EBFjMAixkgixmAREyQRE7CIKVDEL7+oJAPMMwPygvNLQbxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Managers = _t, Worklocations = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Managers", Int64.Type}, {"Worklocations", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","North","Location1;Location2",Replacer.ReplaceText,{"Worklocations"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","South","Location3;Location4",Replacer.ReplaceText,{"Worklocations"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value1", {{"Worklocations", Splitter.SplitTextByDelimiter(";", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Worklocations")
in
#"Split Column by Delimiter"

 

Regards,

Sayali 

 

If this post helps, then please consider Accept it as the solution to help others find it more quickly.

Hi I don’t know power query.  Do you think I can just create a new column and have two values for that field?   How can you do it with measures or calculated column?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.