Forum Discussion

bdehning's avatar
bdehning
Post Prodigy
3 years ago
Solved

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()...
  • sevenhills's avatar
    sevenhills
    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!

     

  • sevenhills's avatar
    sevenhills
    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.