Forum Discussion
Lookup Value from another table based on first date
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 |
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
What I provided was a measure expression. To use it in a column, wrap the whole expression in CALCULATE( ).
Regards,
Pat
5 Replies
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
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
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 What I provided was a measure expression. To use it in a column, wrap the whole expression in CALCULATE( ).
Regards,
Pat
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