topic Re: Find multiple substring DAX CONTAINSSTRING in DAX Commands and Tips

Find multiple substring DAX CONTAINSSTRING

maalsan — Thu, 24 Feb 2022 17:03:58 GMT

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!

Re: Find multiple substring DAX CONTAINSSTRING

Greg_Deckler — Thu, 24 Feb 2022 17:17:04 GMT

maalsan Try:

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

Re: Find multiple substring DAX CONTAINSSTRING

maalsan — Thu, 24 Feb 2022 19:28:05 GMT

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

Re: Find multiple substring DAX CONTAINSSTRING

Greg_Deckler — Thu, 24 Feb 2022 22:59:11 GMT

maalsan This worked for me:

Re: Find multiple substring DAX CONTAINSSTRING

RajaCSN — Fri, 25 Feb 2022 05:24:34 GMT

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.

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

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

Re: Find multiple substring DAX CONTAINSSTRING

maalsan — Fri, 25 Feb 2022 13:53:32 GMT

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!

Re: Find multiple substring DAX CONTAINSSTRING

rusgesig — Tue, 07 Jun 2022 10:50:17 GMT

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

Re: Find multiple substring DAX CONTAINSSTRING

Piotrrekk — Wed, 14 Dec 2022 08:08:52 GMT

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.

Re: Find multiple substring DAX CONTAINSSTRING

RajaCSN — Thu, 15 Dec 2022 16:28:14 GMT

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.

Re: Find multiple substring DAX CONTAINSSTRING

Piotrrekk — Tue, 27 Dec 2022 14:18:18 GMT

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

Re: Find multiple substring DAX CONTAINSSTRING

nirun00s — Thu, 16 Mar 2023 09:28:24 GMT

Awesome.

Re: Find multiple substring DAX CONTAINSSTRING

Alexandernoclue — Wed, 19 Apr 2023 15:48:19 GMT

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

Re: Find multiple substring DAX CONTAINSSTRING

ACZ — Tue, 26 Sep 2023 09:35:26 GMT

Thanks !

Re: Find multiple substring DAX CONTAINSSTRING

Anonymous — Thu, 09 May 2024 10:29:29 GMT

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

Re: Find multiple substring DAX CONTAINSSTRING

diegovelez — Fri, 21 Mar 2025 04:14:57 GMT

For anyone on this forum, Copilot gave me this solution which works quite well:

ContainsAnyValue =
VAR StringList = {"Value1", "Value2", "Value3"} -- Step 1: Define the list of string values
RETURN
IF (
COUNTROWS (
FILTER (
StringList,
CONTAINSSTRING ( [YourColumnName], [Value] )
)
) > 0,
TRUE(),
FALSE()
)