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.
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!
- bdehning3 years agoPost Prodigy
Its close but my How Injury Occurred column is located in Table LossRun
How do I address syntax for that?
- sevenhills3 years agoSuper User
No idea what you are trying to say ...
Can you post what is the issue and its details, please?
- bdehning2 years agoPost Prodigy
I am actually counting each source for my purpose.
Using your Table above and assuming under "The Patient is going under stress" was another row "The Patient is going under trauma"
Is there a way to combine the two strings to get a count of 2 - Patient - Stress/Trauma?