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 trade column contain WERT then equal to South.
- if trade column contain QWE then equal to North.
- if trade column contain North then equal to North.
- if trade column contain South then equal to South.

 

DESIRED OUTPUT

Thank you

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

5 Replies

  • 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"
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    This can also be solved with Power Query.  Just in case you are interested, post bck with data that can be pasted in an MS Excel file.