topic Re: Mapping columns from different tables where one has multiple delimited values in DAX Commands and Tips

Mapping columns from different tables where one has multiple delimited values

user180618 — Wed, 26 Apr 2023 14:03:59 GMT

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?

Re: Mapping columns from different tables where one has multiple delimited values

tamerj1 — Wed, 26 Apr 2023 14:11:09 GMT

Hi user180618
Please try

Matched = 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

Re: Mapping columns from different tables where one has multiple delimited values

user180618 — Wed, 26 Apr 2023 16:12:16 GMT

I'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.

Re: Mapping columns from different tables where one has multiple delimited values

tamerj1 — Wed, 26 Apr 2023 16:19:28 GMT

user180618

Would you post a screenshot of the DAX code that you have used?

Re: Mapping columns from different tables where one has multiple delimited values

user180618 — Wed, 26 Apr 2023 16:38:36 GMT

Never mind, it worked now. I was doing it in Power Query not Power BI.

Thanks very much!

Re: Mapping columns from different tables where one has multiple delimited values

ACS_BIM — Thu, 20 Mar 2025 00:45:47 GMT

I tried this and this is the error I received:

Re: Mapping columns from different tables where one has multiple delimited values

tamerj1 — Thu, 20 Mar 2025 11:01:41 GMT

ACS_BIM

this formula is to be used to create a calculated column. To use it in a query you need to use ADDCOLUMNS