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

[wpf_]

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.

[v-henryk-mstf]

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]

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.

[wpf_]

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?