Transfer values between tables

[Tom_G]

Hello, I’m trying to transfer values from one table to another table, which are structurally different.

The two demo tables are below

Transport Table

Shop Name	Shop ID	Product Name	Driver	Transport ID
A	11111	Cheese	Bill	22221
A	11111	Bread	Bob	22222
A	11111	Bread	Bill	22223
A	11111	Butter	Ben	22224
B	11112	Cheese	Bob	22225
B	11112	Cheese	Ben	22226
B	11112	Bread	Ben	22227
C	11113	Bread	Bill	22228
D	11114	Cheese	Bill	22229
D	11114	Butter	Bob	22230
D	11114	Butter	Ben	22231
D	11114	Butter	Bill	22232

Header Table

Shop Name	Shop ID	Product Name	Bill	Bob	Ben
A	11111	Cheese	Bill
A	11111	Bread	Bill	Bob
A	11111	Butter			Ben
B	11112	Cheese		Bob
B	11112	Bread			Ben
C	11113	Bread	Bill
D	11114	Cheese	Bill
D	11114	Butter	Bill	Bob	Ben

What I want, is to calculate the three columns called “Bill”, “Bob” and “Ben” on the header table as above. And then use them as slicers to filter other data in the header table.

I got part of the way by creating a concatenation of Shop ID, Product Name and Transport ID in the Transport Table and then using the below DAX in a column on the Header table

maxx(filter('Transport' , search('Header'[Shop_ID], 'Transport'[Concat],,0)>0),'Transport'[Concat])

But it doesn’t work in cases where there are more than one driver per product per shop.

Could you please advise how to do this?

Thanks a lot

[Tom_G]

@amitchandak thanks a lot, seems to work well

[amitchandak · Accepted solution]

@Tom_G , Three new columns in DAX in Header table

Bill=
maxx(filter(Transport, Header[Shop Name] =Transport[Shop Name] && header[Shop ID] = Transport[Shop ID]
&& header[Product Name] = Transport[Product Name] && Transport[Driver] ="Bill") , Transport[Driver])

Bob =
maxx(filter(Transport, Header[Shop Name] =Transport[Shop Name] && header[Shop ID] = Transport[Shop ID]
&& header[Product Name] = Transport[Product Name] && Transport[Driver] ="Bob") , Transport[Driver])

Ben =
maxx(filter(Transport, Header[Shop Name] =Transport[Shop Name] && header[Shop ID] = Transport[Shop ID]
&& header[Product Name] = Transport[Product Name] && Transport[Driver] ="Ben") , Transport[Driver])