Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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?
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/
Proud to be a Super User!
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.
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..)
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?
Hi @AlexisOlson
As you said, I realized I had an active relationship between the tables that complicated it. Now works fine. Big thanks!
@jereaallikko , A new column in table one using concatenateX
concatenateX( filter(Table2, Table1[ID] = table2[ID] && Table1[product] = table2[product]), table2[product code])
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].
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |