Adding Key words to Switch

[bdehning]

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(),

SEARCH("Client",[How Injury Occurred],,-1)<>-1,"Client",

"Something else"

)

[sevenhills · Accepted solution]

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!

[bdehning]

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?

[sevenhills]

Source and Source 2 are two different measures. 

You only need one. I provided both syntaxes and let you chose!

[bdehning]

That worked great and I can now see how Dxx would get overwhelmed in a hurry.   

[sevenhills]

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. 

🙂 

[bdehning]

I did accept the two solution you provided.   Can I add mutiple words to each ContainsSring or Search Lines?  Like an or statement?

[sevenhills · Accepted solution]

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]

I create the measure and save and I see this.

[sevenhills]

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. 

[sevenhills]

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"
)​
 

[bdehning]

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"

[sevenhills · Accepted solution]

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!

[bdehning]

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?

[bdehning]

Its close but my How Injury Occurred column is located in Table LossRun

How do I address syntax for that?

[sevenhills]

No idea what you are trying to say ... 

Can you post what is the issue and its details, please?