DAX Measure to Check Tag From Different Table

[jereaallikko]

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

[ValtteriN]

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/

[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 · Accepted 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?

[jereaallikko]

Hi @AlexisOlson

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

[amitchandak]

@jereaallikko , A new column in table one using concatenateX

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

[jereaallikko]

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].