Forum Discussion

KH_Mike's avatar
KH_Mike
Helper III
4 years ago
Solved

Match Serval Text in Measure

Hi All,   Right now, I am using contains function to match the text in "Measure" and show it in "Table". May I know if there is any easy way to acheive the same result ? If possible, is it able to ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi KH_Mike ,

     

    Try

    Measure=
SWITCH (
    MAX ( 'Data'[Country] ),
    "USA", "AMR",
    "CANADA", "AMR",
    MAX ( 'Data'[Country] )
)

    or

    Measure=
IF (
    MAX ( 'Data'[Country] ) = "USA"
        || MAX ( 'Data'[Country] ) = "CANADA",
    "AMR",
    MAX ( 'Data'[Country] )
)

    The MAX function is equivalent to getting the value of the current row, and the MIN function can also achieve the same effect.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
4 years ago

KH_Mike , I think you have to do like, what you are doing

 

 

Measure = if(CONTAINSSTRING(Data,Data[Country],"USA") || CONTAINSSTRING(Data,Data[Country],"CANADA"),"AMR",Data[Country])

KH_Mike's avatar
KH_Mike
Helper III
4 years ago

Hi amitchandak ,

 

I want to know if there is any function like Switch in Excel can make it formula short. Also, if I only input "Data[Country]" at the end. It seems can't work  correctly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi KH_Mike ,

     

    Try

    Measure=
SWITCH (
    MAX ( 'Data'[Country] ),
    "USA", "AMR",
    "CANADA", "AMR",
    MAX ( 'Data'[Country] )
)

    or

    Measure=
IF (
    MAX ( 'Data'[Country] ) = "USA"
        || MAX ( 'Data'[Country] ) = "CANADA",
    "AMR",
    MAX ( 'Data'[Country] )
)

    The MAX function is equivalent to getting the value of the current row, and the MIN function can also achieve the same effect.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • KH_Mike's avatar
      KH_Mike
      Helper III
      4 years ago

      Hi Anonymous,

       

      That exactly what I need. Thank you.