Searching for specific combination of codes from different table

[PowerBI123456]

Hopefully this makes sense, but I am trying to search in column B (my codes) to see if it contains any specific combination of codes from columns A & B from a different table. For example, ID: 11111111 contains codes 12345 & 10112 so thats a match. ID 2222222 contains codes 88373 & 12345 so thats a match. ID 3333333 contains codes 97478 and B5768 so thats a match. 

There are over 400,000 different combinations and I am searching 100,000s of rows. 

Any tips would be appreciated!! 

[Anonymous]

Hi @PowerBI123456 ，

Create a measure as below:

Match = 
var a =SEARCH(MAX('Table (2)'[Column 1 code]),MAX('Table'[Codes]),1,0)
var b=SEARCH(MAX('Table (2)'[Column 2 code]),MAX('Table'[Codes]),1,0)
Return
IF(a<>0&&b<>0,"match","Not found")

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AllisonKennedy]

@PowerBI123456 have updated with correct syntax (it had some ) in wrong places)

I'm thinking something like this could work as calculated COLUMN in Table1: 

Match = IF(COUNTROWS(FILTER(Table2, CONTAINSSTRING(Table1[Codes],Table2[Column 1 Code]) && CONTAINSSTRING(Table1[Codes],Table2[Column 2 Code])))>0, "Match", "Not Found")

[parry2k]

@AllisonKennedy good solution but my only concern will be the performance given the # of rows. @PowerBI123456 let us know if @AllisonKennedy solution performed ok, if not then I have some ideas which I would like to try but first let's try the solution you have in place. Maybe this is a good solution.

[PowerBI123456]

Thanks so much! However, when I tried using that formula, its been running for over 15 minutes now, and still running. 

Anyway to do this faster? Also, any way to bring back what the match was? 

[AllisonKennedy]

@parry2k  You're up - keen to hear your thoughts on optimization. 

@PowerBI123456  what do you mean by bring back what the match was? Do you just want a text value of Column1, Column 2?

[PowerBI123456]

@AllisonKennedy  - yes, anyway to know what the value was from column 1 and column 2?

@Anonymous Thanks a lot! But its not working for me, I am not getting any matches even though I know there are some. 

@parry2k would love to hear your thoughts on optimizing. 

Thanks everyone!!

[Anonymous]

Hi @PowerBI123456 ,

What will return if you use below dax expression:

Match = 
var a =SEARCH(MAX('Table (2)'[Column 1 code]),MAX('Table'[Codes]),1,0)
var b=SEARCH(MAX('Table (2)'[Column 2 code]),MAX('Table'[Codes]),1,0)
Return
a&" "&b

Show me your screenshot..

And have you checked my .pbix file?Can you find the reason why my side has shown the correct result while your side returned error?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[PowerBI123456]

@Anonymous  Should this be a measure or a column?

[Anonymous]

Hi @PowerBI123456 

This is a measure.

What I suggested in the last reply is also a measure,never copy it in a calculated column,otherwise you will get blank values.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[PowerBI123456]

@Anonymous Thanks, but I think the reason why its not working for me is because the column 1 and column 2 have duplicate codes in my file. For example:

[PowerBI123456]

@parry2k Hi, did you have a better way of doing this? Thanks!