Forum Discussion
Adding Key words to Switch
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!
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.
14 Replies
I will do more like this ...
FYI, CONTAINSSTRING is not case-sensitive.
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" )If you want to use Search, you can do as
Source = 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" )​That doesn;t wotk. The first says "The value for 'How Injury Occurred' cannot be determined. Either the column doesn't exist, or there is no current row for this column"
The secomnd ones says "The following syntax error occurred during parsing: Invalid token, Line 9, Offset 2"
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!
I create the measure and save and I see this.
That is the column name in your table.
Check you original post "... I have Source that does a great job in pulling the word Client from a Column "How Injury Occurred" ... "you modify the same measure.
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.
That worked great and I can now see how Dxx would get overwhelmed in a hurry.
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.
🙂
My issue was try to adding your Dax as Source2 and It will not take. I had to Modify my original Source1 Measure to allow it to work?
Source and Source 2 are two different measures.
You only need one. I provided both syntaxes and let you chose!
