Forum Discussion
Helper IIIDAX Measure to Check Tag From Different Table
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
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?
7 Replies
- amitchandak
Super User
jereaallikko , A new column in table one using concatenateX
concatenateX( filter(Table2, Table1[ID] = table2[ID] && Table1[product] = table2[product]), table2[product code])
jereaallikkoHi 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].
- AlexisOlson
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.
- jereaallikko
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..)
- AlexisOlson
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?
- ValtteriN
Community Champion
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/
