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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
maalsan
Advocate I
Advocate I

Find multiple substring DAX CONTAINSSTRING

Hello everyone,

 

I need to find if the substrings "house", "home", "dewelling" appear in a long string column. Using CONTAINSSTRING I am able to find each word at a time- house= CONTAINSSTRING(table1[column1], "house"). Is there a way to look for more than one substring? Perhaps something like house= CONTAINSSTRING(table1[column1], "house" or "home" or "dewelling").

 

Thank you for any help!

1 ACCEPTED SOLUTION

That's weird. It still gives me an error message. 

I found the solution to be simply:

house= CONTAINSSTRING(table1[column1], "house" ) || CONTAINSSTRING(table1[column1], "home" ) || CONTAINSSTRING(table1[column1], "dewelling" )

 Without the need of IF statements. 

 

Thanks!

View solution in original post

13 REPLIES 13
RajaCSN
Helper III
Helper III

Hi maalsan,

 

Assuming that you have table called "String" where Description is your column header; You have another table called Keyword, that contains a column header Keyword.

 

RajaCSN_0-1645766204475.png

You can load both the tables to PowerBI and then for the String Table, you can create a calculated column as below:

 

RajaCSN_1-1645766425807.png

 

Is this providing a solution for your query? If "yes" kindly confirm.

 

Request other experienced users to add value to my suggestion.

 

Best Regards,

 

C.S.N. Raja

 

 

Anonymous
Not applicable

This is a great solution that works fine, however, I struggle to wrap my head around what exactly going on in here.

The part where I'm stuck is the FIRSTNONBLANK () function, and how it manages to "loop through" the column of keywords. When I isolate the expression FIRSTNONBLANK(Table[Column],1) it just returns the first line of the column, but in this solution it obviously checks against all values in the column

If you could explain in plain English, that would be hugely appreciated,
thanks

Thanks !

Hi Raja, im fascinated by your solution. Nevertheless I cant recreate it for myself.
The Search Process works fine, but my Result (when found) gives always ALL values from the Keyword list back.

The rootcause seems to be the "ResultwhenTrue" of the If statement.


EDIT: My Colleague found the problem. You cant have a active relation between the two tables. -.- 

Awesome.

Hi @RajaCSN,
I found your solution very helful, honestly speaking it's mindblowing!
Trying to adjust it to my needs, if I put Keyword column in a slicer giving user option to select one or multiple values. How to adjust DAX so it's still working?
Curent solution is taking all values no meter what is selected in slicer.

Yes, that is a limitation. Let me check if your requirement can be done by me or else we can seek assistance from other experienced users in this community.

Thanks @RajaCSN ,
Initialy it sould like simple request, but so many colequest I reached were not able to assist.

 

Brilliant scaleable solution. This way you only need to edit your keyword list and it will automatically update.

Greg_Deckler
Super User
Super User

@maalsan Try:

house = 
  IF(
    CONTAINSSTRING(table1[column1], "house") ||
    CONTAINSSTRING(table1[column1], "home") ||
    CONTAINSSTRING(table1[column1], "dwelling"),
    TRUE(),
    FALSE()
  )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thanks for the reply. However, it seems that containsstring function only allowes for 2 arguments max. I got the following error message: "Too many arguments were passed to the CONTAINSSTRING function. The maximum argument count for the function is 2".

@maalsan This worked for me:

Greg_Deckler_0-1645743543670.png

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

That's weird. It still gives me an error message. 

I found the solution to be simply:

house= CONTAINSSTRING(table1[column1], "house" ) || CONTAINSSTRING(table1[column1], "home" ) || CONTAINSSTRING(table1[column1], "dewelling" )

 Without the need of IF statements. 

 

Thanks!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors