Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jereaallikko
Helper III
Helper III

DAX 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:

 

Tables.JPG

 

 

 

 

 

 

 

 

 

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:

 

Solution§.JPG

 

Any suggestions?

 

Best Regards,

Jere

1 ACCEPTED SOLUTION

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

AlexisOlson_0-1642623208620.png

 

Do you have relationships between the two tables complicating things?

View solution in original post

7 REPLIES 7
ValtteriN
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




AlexisOlson
Super User
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:

 

Solution1.JPGAs 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.

AlexisOlson_0-1642623208620.png

 

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!

amitchandak
Super User
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])

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors