Forum Discussion
Adding Key words to Switch
- 3 years ago
I gave you the syntax for the column. See tested code, FYI ...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc4xDsIwDAXQq3xl5hJVGWBDggVVHazWaSOSuCQOiNuTdOjgxf5+9jCYKyggMKuLCwi9dxwVpOjFWmbcV9nMeBrMY2Us5QeXQRHdpJKO/o10X6uzRRpU4swJWRPnvKeeUrDSh6GCXFnCWRoBW0srEXh2U2Uk5oO9cBK4uM+t86H5OVBSeJFXu1Mf2tMdvpL8DLH1tHsXxsayeTbj+Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"How Injury Occurred" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"How Injury Occurred", type text}}) in #"Changed Type"Adding two columns in DAX, you only need one. you can chose the one of your interest!
Source = SWITCH( TRUE(), CONTAINSSTRING([How Injury Occurred], "Client"), "Client", CONTAINSSTRING([How Injury Occurred], "Patient"), "Patient", CONTAINSSTRING([How Injury Occurred], "Doctor"), "Doctor", CONTAINSSTRING([How Injury Occurred], "Hero"), "Hero", "Something else" ) Source 2 = SWITCH( TRUE(), SEARCH("Client",[How Injury Occurred],,-1)<>-1, "Client", SEARCH("Patient",[How Injury Occurred],,-1)<>-1, "Patient", SEARCH("Doctor",[How Injury Occurred],,-1)<>-1, "Doctor", SEARCH("Hero",[How Injury Occurred],,-1)<>-1, "Hero", "Something else" )Final output:
Hope this helps!
- 3 years ago
Thanks for accepting the solution.
Defintely you can add,
For "AND" clause you can use "&&"
For "OR" clause you can use "||"
For mix of AND and OR, use brackets and "&&" and "||"
Source = SWITCH( TRUE(), -- Searching for both words Client and Coffee CONTAINSSTRING([How Injury Occurred], "Client") && CONTAINSSTRING([How Injury Occurred], "Coffee"), "Client - Coffee", -- Searching for word "guy" and either of words Actor or Hero CONTAINSSTRING([How Injury Occurred], "guy") && (CONTAINSSTRING([How Injury Occurred], "Hero") || && CONTAINSSTRING([How Injury Occurred], "Actor")) , "Guy - Hero/Actor", -- To combine SEARCH and CONTAINSTRING CONTAINSSTRING([How Injury Occurred], "Doctor") && (SEARCH("medications",[How Injury Occurred],,-1)<>-1), "Doctor - medications", "Something else" )
DAX will get complicated. Try moving to M query if it gets out of hand.
I did accept the two solution you provided. Can I add mutiple words to each ContainsSring or Search Lines? Like an or statement?
Thanks for accepting the solution.
Defintely you can add,
For "AND" clause you can use "&&"
For "OR" clause you can use "||"
For mix of AND and OR, use brackets and "&&" and "||"
Source =
SWITCH(
TRUE(),
-- Searching for both words Client and Coffee
CONTAINSSTRING([How Injury Occurred], "Client")
&& CONTAINSSTRING([How Injury Occurred], "Coffee"), "Client - Coffee",
-- Searching for word "guy" and either of words Actor or Hero
CONTAINSSTRING([How Injury Occurred], "guy")
&& (CONTAINSSTRING([How Injury Occurred], "Hero")
|| && CONTAINSSTRING([How Injury Occurred], "Actor"))
, "Guy - Hero/Actor",
-- To combine SEARCH and CONTAINSTRING
CONTAINSSTRING([How Injury Occurred], "Doctor")
&& (SEARCH("medications",[How Injury Occurred],,-1)<>-1), "Doctor - medications",
"Something else"
)
DAX will get complicated. Try moving to M query if it gets out of hand.