topic Using a comma separated column to lookup a value in another unrelated table in DAX Commands and Tips

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

Craigo — Fri, 04 Aug 2023 10:15:15 GMT

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 a new column in the first table. For example (this is a VERY simplified version of what Im after), I have a table

And I want to take each of the “Sales_Codes” and look for them in the “item_code” column in the table below and if it exists in “item_code”, return the “item_desc”

So the result would look like

Because of the ways the tables are created, there would never be a possibility of an ID being more than one of the “item_desc” values (i.e 453124 would only be “car” and nothing else, but I have to look it up to know that it is “car”). Any help with this would be greatly appreciated.

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

Anonymous — Mon, 07 Aug 2023 07:35:59 GMT

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.

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

barritown — Mon, 07 Aug 2023 08:18:02 GMT

Hi Craigo,

There is also such an option:

- split the comma separated lists in the [Item_Code] column of your second table into separate rows using this solution - https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculated-column-with-comma-delimited-text-split-into-rows-in-a/m-p/1494014;

- once done, the following calculated column does the rest:

Item_desc = MINX ( FILTER ( Ref, CONTAINSSTRING ( [Sales_Code], [Item_Code] ) ), [Item_desc] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

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

Craigo — Mon, 07 Aug 2023 22:15:21 GMT

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.