Power BI: search a word in different tables

There are different ways to look for a specific word in two tables, the only difference is that one will work better than the others based on my data. I have those 2 tables:

Table1 Table2
power bi power bi

I will resume quickly what I can do with table1 only:

  • SEARCH
    • With 1 criteria
      IF(ISERROR(SEARCH("XXX",'table'[argument])),"not found","found")
      power bi
    • With 2 or more
      IF(ISERROR(SEARCH("XXX",'table'[argument1]) && SEARCH("YYY",'table'[argument2])),"not found","found")
      power bi
  • CONTAINSSTRING
    • With 1 criteria
      IF(CONTAINSSTRING('table'[argument],"XXX"),"found","no found")
      power bi
    • With 2 or more
      IF(CONTAINSSTRING('table'[argument1],"XXX") && CONTAINSSTRING('table'[argument2],"YYY"),"found","no found")
      power bi

NOTE: replace “table” and “argument” by yours and XXX and YYY by the values to find

power bi

With the 2 tables, the easier is to use:

  • LOOKUPVALUE
    • With 1 criteria
      LOOKUPVALUE('table1'[argument1],'table1'[argument2],'table2'[argument2])
      power bi
    • With 2 or more
      LOOKUPVALUE('table1'[argument1],'table1'[argument2],'table2'[argument2],'table1'[argument3],
      'table2'[argument3])
      power bi

NOTE: replace “table1” and “table2” by yours, “argument1” by the result column and “argument2” and “argument3” by the columns to match

As we can see, it is not looking the word into a sentence or a text, so I will use one of those 2 formulas:

  • With SEARCH
    LOOKUPVALUE('table2'[argument1],'table2'[argument2],FIRSTNONBLANK(FILTER(VALUES('table2'[argument2]),
    SEARCH('table2'[argument2],'table1'[argument2],1,0)),1))
    power bi
  • With CONTAINSSTRING
    LOOKUPVALUE('table2'[argument1],'table2'[argument2],FIRSTNONBLANK(FILTER(VALUES('table2'[argument2]),
    CONTAINSSTRING('table1'[argument2],'table2'[argument2])),1))
    power bi
power bi

NOTE: for the cells which are blank, if I want to put a value, I have to use IF and ISBLANK functions like that:

IF(ISBLANK(formula),"not found",formula)

There are other ways to get the same results, based on my data, one will work better than the others, for instance:

  • MINX(FILTER('table2',SEARCH('table2'[argument2],'table1'[argument2],1,0)>0),'table2'[argument1])
    power bi
    NOTE: instead of MINX, I can use MAXX
    power bi
  • CONCATENATEX(FILTER('table2',CONTAINSSTRING('table1'[argument2],'table2'[argument2])),'table2'[argument1],",")
    power bi
  • IF(CALCULATE(COUNTROWS('table1'),FILTER('table1',SEARCH('table2'[argument2],'table1'[argument2],1,0)>0))>0,
    "found","not found")
    power bi
  • IF(COUNTROWS(FILTER('table2',CONTAINSSTRING('table1'[argument2],'table2'[argument2]))),"found","not found")
    power bi
  • IF('table2'[argument2] in SELECTCOLUMNS(RELATEDTABLE('table1'),'table1'[argument2]),"found","not found")
    power bi
power bi

All formulas above can return the values of any columns of the Table2 but if I want to get only the ones from the “number” column which have the same values as the “incident” column of the Table1, one of those 2 formulas will work better:

  • With SEARCH
    FIRSTNONBLANK(FILTER(VALUES('table2'[argument]),SEARCH('table2'[argument],'table1'[argument],1,0)),1)
    power bi
  • With CONTAINSSTRING
    FIRSTNONBLANK(FILTER(VALUES('table2'[argument]),CONTAINSSTRING('table1'[argument],'table2'[argument])),1)
    power bi
power bi

Interesting Management