DAX Measure or Calculated Column for identifying match between two tables (one column exact match, o

[melimob1]

Hi 

Need help with...

DAX Measure or Calculated Column for identifying match between two tables (one column exact match, other partial substring/Keyword match)

I have this scenario:

Table 1 (source table)

Customer	Country	String
a	UK	Apples, oranges
b	USA	Pear, Orange
c	UK	Apple
d	UK	Pear, Orange

Table 2 (match/lookup table)

Country	Keyword
UK	Apple
USA	Orange

I want to lookup Table 1 Country against Table 2 Country, then search for table2'keyword' against table1'string' column to return where it is found/not found.

E.g. 

Result would be to highlight 

All customers who do not match Keyword and which countries they're in 

E.g.

Result

Country	Keyword	Count of customer MATCH	Count of customer MIS MATCH	Count of customer that have multi values
UK	Apple	2 (i.e. (a,c)	1 (d)	2 (a,d)
USA	Orange	1	0	1

Would need it so I can get the list of those customers in drill down also.

Usually in PQ, I would split string to a list, concat and then compare but I'm sure there is an easier way to do this?

Data is one thing but any suggestions on a nice visual to demonstrate this would also be appreciated.

thank you in advance! 

[ryan_mayu · Accepted solution]

you can try this

Column = calculate(COUNTROWS('Table'),FILTER('Table',CONTAINSSTRING('Table'[String],'Table (2)'[Keyword])&&'Table'[Country]='Table (2)'[Country]))

Column 2 = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Country]='Table (2)'[Country]))-'Table (2)'[Column]

Column 3 = CALCULATE(COUNTROWS('Table'),FILTER('Table',CONTAINSSTRING('Table'[String],",")&&'Table'[Country]='Table (2)'[Country]))

pls see the attachment below

[melimob1]

thank you so much Ryan

I managed to apply and the result worked.  I then also merged the 'keyword' column into my source table and did an if statement so I could tag each row and use it not only as counts but in a visual sliced different ways.  Really appreciate you taking the time.

thank you!

[ryan_mayu]

you are welcome, pls accept the answer as the solution. Thanks

[ExcelMonke]

Hello,
You can consider the following DAX:

MatchFlag = 
IF(Table1[Keyword] IN VALUES (Table2[Keyword]),1,0)

[melimob1]

thank you but would this also consider matching on the country then finding the relevant keyword?