topic Re: DAX Measure to Check Tag From Different Table in DAX Commands and Tips

DAX Measure to Check Tag From Different Table

jereaallikko — Wed, 19 Jan 2022 08:48:57 GMT

Hi all,

I am trying to solve a problem with relationships and column mapping from other table. I have two tables, both containing hundreds of rows of data. I have created a simple illustration data below. Table1 is considered as Main Data table, whereas Table2 as Product Code mapping Table:

I would like to add Product Code column to Table 1. So basically, I am looking for a solution (DAX, Relationships, etc.) to check if Product in Table2 is included in Product of Table1. If it is true, then it should map to the correct Product Code. I have tried to play with relationships, but it does not map correctly.

As a result, all "Bert" Products should have a code FEB, "WayX" Products should have a code DCA etc...

A desired outcome example:

Any suggestions?

Best Regards,

Jere

Re: DAX Measure to Check Tag From Different Table

amitchandak — Wed, 19 Jan 2022 10:08:35 GMT

jereaallikko , A new column in table one using concatenateX

concatenateX( filter(Table2, Table1[ID] = table2[ID] && Table1[product] = table2[product]), table2[product code])

Re: DAX Measure to Check Tag From Different Table

jereaallikko — Wed, 19 Jan 2022 12:00:03 GMT

Hi amitchandak,

It didn't work. After a brief investigation, this measure works only if Table1[ID] = Table2[ID]. This should somehow be modified to link between "Product" columns. To Table1 a column or DAX like : Table2[Product] "contains" Table1[Product], then Table2[Product Code].

Re: DAX Measure to Check Tag From Different Table

AlexisOlson — Wed, 19 Jan 2022 16:26:31 GMT

You might be able to do this with a calculated column like this:

VAR Prod1 = Table1[Product] RETURN CONCATENATEX ( CALCULATETABLE ( VALUES ( Table2[Product Code] ), FILTER ( Table2, CONTAINSSTRING ( Prod1, Table2[Product] ) ) ), Table2[Product Code] )

However, I'd recommend against models that have columns with multiple values concatenated together. It makes measures harder to write and performance will suffer if your datasets get large.

Re: DAX Measure to Check Tag From Different Table

jereaallikko — Wed, 19 Jan 2022 17:29:37 GMT

Hi AlexisOlson,

Thanks for the reply.

This solution is one step closer. Problem is that this works only for those where Table1[Product] = Table2[Product]. Not if the field in Table1[Product] contains more characters, or two products. See below:

As you can see, it does not map for differently filled fields (Bert.5, XMAN_1, Bert.5 etc..)

Re: DAX Measure to Check Tag From Different Table

ValtteriN — Wed, 19 Jan 2022 19:17:15 GMT

Hi jereaallikko ,

If possible I would do this always in Powerquery. I just had a discussion about a similar topic yesterday with my team members. So you can create conditiona column in PQ using this logic:

Text.Contains(MyText, "car", Comparer.OrdinalIgnoreCase)

Lopuksi pieni markkinointi 😀. PowerBI konsultointia + mielenkiintoinen embedded ratkaisu: https://get.bibook.com/

Re: DAX Measure to Check Tag From Different Table

AlexisOlson — Wed, 19 Jan 2022 20:14:20 GMT

I'm not sure what you're doing wrong but it works fine for me.

Do you have relationships between the two tables complicating things?

Re: DAX Measure to Check Tag From Different Table

jereaallikko — Thu, 20 Jan 2022 06:33:12 GMT

Hi AlexisOlson

As you said, I realized I had an active relationship between the tables that complicated it. Now works fine. Big thanks!