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 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"
)
- bdehning3 years agoPost Prodigy
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"
- sevenhills3 years agoSuper User
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!
- bdehning3 years agoPost Prodigy
Its close but my How Injury Occurred column is located in Table LossRun
How do I address syntax for that?