Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Charlott_9
New Member

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(SEARCH) function to be able to make my matches.
Ex: if(SEARCH("CE ",'Table',1,0), "Centre").
However, I can't add an OR function to my SEARCH function.
I've tried this: if(SEARCH(OR("CE ", "Région Centre"),'Table',1,0), "Centre"), but it doesn't work.
Do you know how to do this?

1 ACCEPTED SOLUTION

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

vcgaomsft_0-1697009799911.png

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

View solution in original post

3 REPLIES 3
mussaenda
Community Champion
Community Champion

Hi @Charlott_9 ,

 

have you tried containstring function?

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,

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

vcgaomsft_0-1697009799911.png

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.