Forum Discussion
DAX Measure or Calculated Column for identifying match between two tables (one column exact match, o
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!
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
5 Replies
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 belowthank 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!
you are welcome, pls accept the answer as the solution. Thanks
Hello,
You can consider the following DAX:MatchFlag = IF(Table1[Keyword] IN VALUES (Table2[Keyword]),1,0)thank you but would this also consider matching on the country then finding the relevant keyword?
