Forum Discussion
bdehning
3 years agoPost Prodigy
Adding Key words to Switch
I have Source that does a great job in pulling the word Client from a Column "How Injury Occurred" How do I add more key words to this. I want to start with Patient? Source = SWITCH(TRUE()...
- 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.
bdehning
3 years agoPost Prodigy
That worked great and I can now see how Dxx would get overwhelmed in a hurry.
- sevenhills3 years agoSuper User
Glad it all worked.
In my view, DAX was developed with primarily for biz users. Later, got into too much technical, and getting complicated and overwhelmed as it grows.
🙂