Forum Discussion

Craigo's avatar
Craigo
Regular Visitor
3 years ago
Solved

Using a comma separated column to lookup a value in another unrelated table

Hello. I have one table with comma separated column and I want to look up each value in another unrelated table and if it exists, get the value from a different column in that second table and create...
comma seperated
DAX
  • Craigo's avatar
    Craigo
    3 years ago

    Mate, that works brilliantly. I'll do some more testing with a slightly more complex set of tables, but I think this will work. Thanks very much for your help.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Craigo ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create calculated columns.

 

PATH = SUBSTITUTE([Sales_Code],",","|")
PATH_1 = PATHITEM([PATH],1)
PATH_2 = PATHITEM([PATH],2)
PATH_3 = PATHITEM([PATH],3)
Item_desc = MAXX(FILTER(Table2,CONTAINSSTRING([Item_Code],EARLIER(Table1[PATH_1]))||IF(EARLIER(Table1[PATH_2])<>BLANK(),CONTAINSSTRING([Item_Code],EARLIER(Table1[PATH_2])))||IF(EARLIER(Table1[PATH_3])<>BLANK(),CONTAINSSTRING([Item_Code],EARLIER(Table1[PATH_3])))),[Item_desc])

 

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.