cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gourangshah24
Frequent Visitor

DAX function to identify different searches within a text irrespective of case sensitive

Hi Everyone,

 

This is my first post. Hope I am able to explain my query. I am basically trying to create a DAX column that mentions "COVID-19" based on the search performed in another column. The issue is that the formula that I use, I have to think and define each type that people can mention COVID-19 related expenses. For eg: Some may write COVID19,Covid19, CoVid19, COVID 19, COVID-19 etc. I need to put up a formula that can search COVID word within the text irrespective of being case sensitive and give me the answer as "COVID-19". 

 

Currently, I use this formula:

C_COVID-19 =
SWITCH(TRUE(),
FIND("COVID",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
FIND("covid",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
FIND("Covid",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
FIND("CoVid",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
FIND("COVID 19",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
FIND("covid 19",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
FIND("COVID-19",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
FIND("covid-19",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
FIND("COVID19",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
FIND("covid19",Expenses[Purpose of Expense], 1, 0) >0, "COVID-19",
"OTHER")
 

Is there a simple formula that I can use with minimal variations and that can give me the same result as above instead of defining each circumstance.Covid-19.JPG

I hope to have been able to address my query. I just want to have a better formula which can search the word Covid or covid 19 and return COVID-19 in the DAX column irrespective of being case sensitive.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: DAX function to identify different searches within a text irrespective of case sensitive

@gourangshah24 try this

 

IF ( CONTAINSSTRING ( Table[ColumnName], "Covid" ), "Covid-19", "Other" )

 

Would appreciate Kudos 🙂 if my solution helped.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

1 REPLY 1
Super User IV
Super User IV

Re: DAX function to identify different searches within a text irrespective of case sensitive

@gourangshah24 try this

 

IF ( CONTAINSSTRING ( Table[ColumnName], "Covid" ), "Covid-19", "Other" )

 

Would appreciate Kudos 🙂 if my solution helped.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors