The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
What I provided was a measure expression. To use it in a column, wrap the whole expression in CALCULATE( ).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
What I provided was a measure expression. To use it in a column, wrap the whole expression in CALCULATE( ).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
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 |
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |