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 |
I will resume quickly what I can do with table1 only:
- SEARCH
- With 1 criteria
IF(ISERROR(SEARCH("XXX",'table'[argument])),"not found","found")
- With 2 or more
IF(ISERROR(SEARCH("XXX",'table'[argument1]) && SEARCH("YYY",'table'[argument2])),"not found","found")
- With 1 criteria
- CONTAINSSTRING
- With 1 criteria
IF(CONTAINSSTRING('table'[argument],"XXX"),"found","no found")
- With 2 or more
IF(CONTAINSSTRING('table'[argument1],"XXX") && CONTAINSSTRING('table'[argument2],"YYY"),"found","no found")
- With 1 criteria
NOTE: replace “table” and “argument” by yours and XXX and YYY by the values to find
With the 2 tables, the easier is to use:
- LOOKUPVALUE
- With 1 criteria
LOOKUPVALUE('table value return'[argument value return],'table value return'[argument value match],'table value compare'[argument value match])
NOTE: instead of the 'table value compare'[argument value match], I can put a specific criteria. For instance:
LOOKUPVALUE('table value return'[argument value return],'table value return'[argument value match],"criteria")
- With 2 or more
LOOKUPVALUE('table1'[argument1],'table1'[argument2],'table2'[argument2],'table1'[argument3],
'table2'[argument3])
- With 1 criteria
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))
- With CONTAINSSTRING
LOOKUPVALUE('table2'[argument1],'table2'[argument2],FIRSTNONBLANK(FILTER(VALUES('table2'[argument2]),
CONTAINSSTRING('table1'[argument2],'table2'[argument2])),1))
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])
NOTE: instead of MINX, I can use MAXX
- CONCATENATEX(FILTER('table2',CONTAINSSTRING('table1'[argument2],'table2'[argument2])),'table2'[argument1],",")
- IF(CALCULATE(COUNTROWS('table1'),FILTER('table1',SEARCH('table2'[argument2],'table1'[argument2],1,0)>0))>0,
"found","not found")
- IF(COUNTROWS(FILTER('table2',CONTAINSSTRING('table1'[argument2],'table2'[argument2]))),"found","not found")
- IF('table2'[argument2] in SELECTCOLUMNS(RELATEDTABLE('table1'),'table1'[argument2]),"found","not found")
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)
- With CONTAINSSTRING
FIRSTNONBLANK(FILTER(VALUES('table2'[argument]),CONTAINSSTRING('table1'[argument],'table2'[argument])),1)
Interesting Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...