Forum Discussion

maalsan's avatar
maalsan
Advocate I
4 years ago
Solved

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(tab...
CONTAINSSTRING
find
string
Substring
Text
  • maalsan's avatar
    maalsan
    4 years ago

    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!

RajaCSN's avatar
RajaCSN
Advocate II
4 years ago

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

 

 

  • Piotrrekk's avatar
    Piotrrekk
    Frequent Visitor
    3 years ago

    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.

    • RajaCSN's avatar
      RajaCSN
      Advocate II
      3 years ago

      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.

      • Piotrrekk's avatar
        Piotrrekk
        Frequent Visitor
        3 years ago

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

         

  • rusgesig's avatar
    rusgesig
    Helper IV
    4 years ago

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

  • nirun00s's avatar
    nirun00s
    Helper I
    3 years ago

    Awesome.

  • Alexandernoclue's avatar
    Alexandernoclue
    Frequent Visitor
    3 years ago

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

  • ACZ's avatar
    ACZ
    New Member
    2 years ago

    Thanks !

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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