Forum Discussion

Charlott_9's avatar
Charlott_9
New Member
2 years ago
Solved

OR function in an IF(SEARCH) function

Hello, I have region labels that always contain the same characters. Ex: "CE" for the "Centre" region, but sometimes for this region, it simply says "Région Centre" instead of "CE". I made an IF(SE...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Charlott_9 ,

    Please try like:

    IF (
    SEARCH ( "CE", 'Table'[ColName], 1, BLANK () ) || 
    SEARCH ( "Région Centre", 'Table'[ColName], 1, BLANK () ) ||
    <search3> ||
    ...
    <search30>,
    "Centre",
    ""
)

    The dax function is not very good at doing this, so if you can consider using the m code in powerquery.
    Custom column:

    if List.MatchesAny({"CE","Région Centre","OtherTextYouWant"},(x)=> Text.Contains([Column1],x)) then "Centre" else ""

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

mussaenda's avatar
mussaenda
Community Champion
2 years ago

Hi Charlott_9 ,

 

have you tried containstring function?

Charlott_9's avatar
Charlott_9
New Member
2 years ago

Hi mussaenda 

Before using the SEARCH function, I looked at how the function CONTAINSSTRING worked, and I wasn't sure It would have solved my problem. 

The SEARCH function answers to my first request which is to find some words in all my characters strings, and I have many matches to do (like maybe more than 30). But I would like to add an other possibility in my function SEARCH (Ex : "CE" OR "Centre" and not only "CE" --> Because the match will be "Centre" for this 2 words), to limit the number of IF in my formula.

Maybe I didn't well understand how to use CONTAINSSTRING function. Could you please tell me how it would be useful to use it?

Thanks for your help,

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Charlott_9 ,

    Please try like:

    IF (
    SEARCH ( "CE", 'Table'[ColName], 1, BLANK () ) || 
    SEARCH ( "Région Centre", 'Table'[ColName], 1, BLANK () ) ||
    <search3> ||
    ...
    <search30>,
    "Centre",
    ""
)

    The dax function is not very good at doing this, so if you can consider using the m code in powerquery.
    Custom column:

    if List.MatchesAny({"CE","Région Centre","OtherTextYouWant"},(x)=> Text.Contains([Column1],x)) then "Centre" else ""

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum