Lookup Value from another table based on first date

[Josh97Ellis]

Hello Community, 

I am trying to lookup a value in a child table based on the first delivery date. The tricky part of this is that there will be multiple delivery numbers for each distinct value in the parent table. From the parent table, I want to retrieve the first order number from the child table based on the first delivery date. 

This is what I am building (Parent):

Order Shipping Point ID	First Delivery Number (NEED)
7685590 | 3915	1111
7686263 | 39SP	2221

This is what I have (Child): --- Highlighted in red is the row I am needing

Order Shipping point ID	Delivery Number	Delivery Date
7685590 | 3915	1111	1/1/2021
7685590 | 3915	1112	1/3/2021
7685590 | 3915	1113	1/5/2021
7686263 | 39SP	2221	1/2/2021
7686263 | 39SP	2222	1/4/2021

[mahoneypat · Accepted solution]

Please try this measure expression, replace Child for your actual child table name.

First Delivery Number = FIRSTNONBLANKVALUE(Child[Delivery Date], MIN(Child[Deliver Number]))

Regards,

Pat

[mahoneypat · Accepted solution]

What I provided was a measure expression.  To use it in a column, wrap the whole expression in CALCULATE( ).

Regards,

Pat

[Josh97Ellis]

@mahoneypat 
That was the trick, thank you, that works perfectly!

I did come up with an alternative solultion by using this code and just filter on 1 for any calculation

[Josh97Ellis]

@mahoneypat 

The green column (Excel) is the field I am refering to from the parent table (PBI).

I want to lookup the delivery number (blue) for only the first delivery created date (Orange) based on the Order shipping point ID (Green).

Lookup in PowerBi should be: 

Order shipping point	First Delivery
7686639 | 3915	53190034
7686339 | 39SP	53191851

[mahoneypat · Accepted solution]

Please try this measure expression, replace Child for your actual child table name.

First Delivery Number = FIRSTNONBLANKVALUE(Child[Delivery Date], MIN(Child[Deliver Number]))

Regards,

Pat

[Josh97Ellis]

@mahoneypat 

Doing this as a calculated column, I get a delivery nyumber from the child table. However, the delivery number that is being populated is not correct and is not even part of the corresponding order number