Forum Discussion
Helper IMapping columns from different tables where one has multiple delimited values
I have two tables that I would like to map together, either using a join or relationship or a lookup (not sure which is most appropriate). In Table 1 I have a column that lists English words/slang and the different ways they are said across US, UK and Australian English separated by commas:
| Column1.words |
| cigarettes, cigs, butts, f*gs, durry |
| tracksuit bottoms, tracksuits, sweatpants, trackies, dacks |
In Table 2, I have the categorisations of the words by US, UK and Australian:
| US_english | UK_english | AU_english |
| cigs | f*gs | durry |
| candy | sweets | lollies |
| sweatpants | trackies | trackies |
I want to have a Column2 in Table 1 that pulls out a word from the list Column1 list (cigarettes, cigs...) based on the US_english table column in Table 2, so that my Table 1 then has two columns like this:
| Column1.words | Column2.matched |
| cigarettes, cigs, butts, durry | cigs |
| tracksuit bottoms, tracksuits, sweatpants, trackies, dacks | sweatpants |
What would be the best way to do this?
Hi user180618
Please tryMatched = MAXX ( FILTER ( VALUES ( Table2[US_english] ), CONTAINSSTRING ( Table1[Word], Table2[US_english] ) ), Table2[US_english] )The MAXX shall not be required and can be removed
6 Replies
- tamerj1
Community Champion
Hi user180618
Please tryMatched = MAXX ( FILTER ( VALUES ( Table2[US_english] ), CONTAINSSTRING ( Table1[Word], Table2[US_english] ) ), Table2[US_english] )The MAXX shall not be required and can be removed
user180618I'm getting this error: Expression.Error: The name 'MAXX' wasn't recognized. Make sure it's spelled correctly.
When I remove MAXX I get a 'Token RightParen expected' error on the comma in the third-last line.
- tamerj1
Would you post a screenshot of the DAX code that you have used?
I tried this and this is the error I received:
