Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Contain Letter/Word

Hi good day, Can someone pls need help on my calculated column. pls refer below  - if trade column contain AAMM then equal to North.  - if trade column contain SNMJ then equal to South.  - if tra...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi AllanBerces 

    Not if you use CONTAINSSTRING. However, the one below should work:

    SWITCH (
    TRUE (),
    LEFT ( 'table'[Trade], 4 ) IN {"AAMM", "QWE"} 
        || 'table'[Trade] = "North", "North",
    CONTAINSSTRING ( 'table'[Trade], "SNMJ" )
        || 'table'[Trade] = "South", "South",
    "South", CONTAINSSTRING ( 'table'[Trade], "WERT" ),
    "South", CONTAINSSTRING ( 'table'[Trade], "QWE" ),
    "North"
)
danextian's avatar
danextian
Super User
1 year ago

Hi AllanBerces 

 

Please try this:

SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'table'[Trade], "AAMM" )
        || 'table'[Trade] = "North", "North",
    CONTAINSSTRING ( 'table'[Trade], "SNMJ" )
        || 'table'[Trade] = "South", "South",
    "South", CONTAINSSTRING ( 'table'[Trade], "WERT" ),
    "South", CONTAINSSTRING ( 'table'[Trade], "QWE" ),
    "North"
)
  • AllanBerces's avatar
    AllanBerces
    Post Prodigy
    1 year ago

    Hi danextian thank you for the reply, just question possible to used IN since the output are same for multiple prefix {"AAMM", "QWE"} = North

     

    Thank you

    • danextian's avatar
      danextian
      Super User
      1 year ago

      Hi AllanBerces 

      Not if you use CONTAINSSTRING. However, the one below should work:

      SWITCH (
    TRUE (),
    LEFT ( 'table'[Trade], 4 ) IN {"AAMM", "QWE"} 
        || 'table'[Trade] = "North", "North",
    CONTAINSSTRING ( 'table'[Trade], "SNMJ" )
        || 'table'[Trade] = "South", "South",
    "South", CONTAINSSTRING ( 'table'[Trade], "WERT" ),
    "South", CONTAINSSTRING ( 'table'[Trade], "QWE" ),
    "North"
)
      • AllanBerces's avatar
        AllanBerces
        Post Prodigy
        1 year ago

        Hi danextian thank you very much working perfectly