Dax to find correct name from two tables

[dhanekula1996]

Hello All,
I have two data sets one is the ALL orders Table which contains both Product delivery and Product return records and the other table is the Return Data table which contains info about products which only returned by customers.

I am trying to get the right Delivery driver's name and Pickup driver's name from these two data sets.

Output: Adding a Delivery driver's name and Pickup driver's name columns to the Returns table.

The sample dataset and explanation using various scenarios are as follows:

 Scenario 1:
1. The Order number contains multiple products and was delivered initially by the driver, John (delivery driver), on 09/21/2022. The customer returned all products The **bleep** up driver ( Max) on 09/23/2022.
2. After that the new products are delivered again to the customer on 09/25/2022 by Jose (New Delivery Driver). The customer returns one product (A1234) on 09/26/2022 to the picky driver ( Carlos).
So I want to show respective Drivers based on the date.

Scenario 2:
The driver picked up a return that was initially delivered 2 years back. Therefore The orders table doesn't contain data. So the Delivery measure should show blank.

Scenario 3: 
1. Driver picks up the product on 09/27/2022  but he updates the system on 09/28/2022.

The sample data:

All Orders Table:

Order No	Item Number	Date	Driver Name
85629	A1234	9/21/2022	John	Scenario 1
85629	B123	9/21/2022	John	Scenario 1
85629	C1245	9/21/2022	John	Scenario 1
85629	A1234	9/23/2022	Max	Scenario 1
85629	B123	9/23/2022	Max	Scenario 1
85629	C1245	9/23/2022	Max	Scenario 1
85629	A1234	9/25/2022	Jose	Scenario 1
85629	B123	9/25/2022	Jose	Scenario 1
85629	C1245	9/25/2022	Jose	Scenario 1
85629	A1234	9/26/2022	Carlos	Scenario 1
68956	D-1898	9/26/2022	Steve	Scenario 2
58694	F898	9/26/2022	Daniel	Scenario 3
58694	F898	9/27/2022	Miguel	Scenario 4

* Scenario's are not in original table used for explanation.

Return Table:

Order No	Item Number	Return Date	Delivery Driver Name	PickUp Driver Name
85629	A1234	9/23/2022	John	Max	Scenario 1
85629	B123	9/23/2022	John	Max	Scenario 1
85629	C1245	9/23/2022	John	Max	Scenario 1
69523	A1234	9/26/2022	Jose	Carlos	Scenario 2
68956	D-1898	9/26/2022		Steve	Scenario 3
58694	F898	9/28/2022	Daniel	Miguel	Scenario 4

Thank you so much for your help.
-Shashi
@amitchandak 

@Greg_Deckler 

@parry2k 

[v-rongtiep-msft]

Hi @dhanekula1996 ,

The [Delivery Driver Name] and the PickUp Driver Name] columns are your desired output?  Although you explained it in great detail, I still have doubts.

The [item number] is C1245. There are three rows. Why output like the following?

85629

C1245

9/23/2022

John

Max

Scenario 3

Instead of the following?

Please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

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

[dhanekula1996]

Hello Sir,
Thank you for your response. Yes, those are the columns I am looking to add to my Returns table based on Iten and order number. And I have also edited the question to make it clearer.
The out of 3 records of C1245 only the the record on 09/23/2022 is available because it was return made by customer.
Ex:

1. c1245 09/21/2022  ------------ Intial Product Delivery
2. c1245 09/23/2022  ------------ Customer Returned the product
3. c1245 09/25/2022  ------------ Same product Re-delivery

And I cannot have the output as you mentioned because I have no way of identiying the record is a return or delivery just with All order column. I need to compare the records in both the tables to identify a respective data for a return.