Forum Discussion
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 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.
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.
3 Replies
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.
- barritown
Solution Sage
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
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.
