cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dhanekula1996
Helper I
Helper I

Dax to find correct name from two tables

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 DateDriver Name 
85629A12349/21/2022JohnScenario 1
85629B1239/21/2022JohnScenario 1
85629C12459/21/2022JohnScenario 1
85629A12349/23/2022MaxScenario 1
85629B1239/23/2022MaxScenario 1
85629C12459/23/2022MaxScenario 1
85629A12349/25/2022JoseScenario 1
85629B1239/25/2022JoseScenario 1
85629C12459/25/2022JoseScenario 1
85629A12349/26/2022CarlosScenario 1
68956D-18989/26/2022SteveScenario 2
58694F8989/26/2022DanielScenario 3
58694F8989/27/2022MiguelScenario 4

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

Return Table:

Order No Item Number Return DateDelivery Driver NamePickUp Driver Name 
85629A12349/23/2022JohnMaxScenario 1
85629B1239/23/2022JohnMaxScenario 1
85629C12459/23/2022JohnMaxScenario 1
69523A12349/26/2022JoseCarlosScenario 2
68956D-18989/26/2022 SteveScenario 3
58694F8989/28/2022Daniel MiguelScenario 4

 

 

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

@Greg_Deckler 

@parry2k 

2 REPLIES 2
v-polly-msft
Community Support
Community Support

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?

vpollymsft_0-1664330396173.png

 

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.

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.


 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors