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(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!

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!

14 Replies

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

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    maalsan Try:

    house = 
  IF(
    CONTAINSSTRING(table1[column1], "house") ||
    CONTAINSSTRING(table1[column1], "home") ||
    CONTAINSSTRING(table1[column1], "dwelling"),
    TRUE(),
    FALSE()
  )
    • maalsan's avatar
      maalsan
      Advocate I
      4 years ago

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

  • diegovelez's avatar
    diegovelez
    Frequent Visitor
    1 year ago

    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()
    )