How to compare 2 different columns and return a different column as an Output

[Anonymous]

Hi Community , 

There are 2 tables which are joined by Column ID . I want to compare each cell value of Complaint Reference in table A against all values of Table B and if there is a match ( no need to be exact match , but even if some part of the string matches)  , then I want to return ID column as an output from Table A . 

Table A 

Table B 

Please assist with the requirement . 

Thanks

[danextian · Accepted solution]

Hi @Anonymous 

There are multiple ways to achieve your desired result but you definitely cannot use a relationsip or LOOKUPVALUE as there is no exact match between the two columns in question. I have attached two sample pbix files - one uses power query, the other uses DAX (both calc column and measure)

I have atta

[Anonymous]

Thank you @danextian  . I Will look into this solutions and try them. Appreciate your help .