Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
We we issue purchase orders on Dynamics 365, the purchase order lines contain a count of the number of units ordered (but not yet received). As folks do full or partial goods receipts, these are written to another table - saying how many were received and how many are still left to arrive. So i've built a table of the last receipt order lines, each of which tells me how many items are still on backlog. So in each purchase order line record, I have a new column "RemainingQty", which I set using:
Solved! Go to Solution.
Thank you - I managed to nest a second LOOKUPVALUE and job done - all working. Thank you.
Thank you - I managed to nest a second LOOKUPVALUE and job done - all working. Thank you.
I'm happy you could solve it 👍.
Can you mark the post as solution, to make it easier for the person to find a solution in case of a similar issue?
If you have any questions let me know.
Best regards
Denis
Hey @IanWaring ,
why don't you just check if the LOOKUP gives you a result and otherwise give back the OrderedPurchaseQuantity:
RemainingQty =
VAR vAlreadyReceived =
LOOKUPVALUE(
Remaining[RemainingPurchaseQuantity],
Remaining[POandLine], PurchaseOrderLinesV2[POPlusLine],
0
)
VAR vOrdered =
MAX( PurchaseOrderLinesV2[OrderedPurchaseQuantity] )
RETURN
IF(
vAlreadyReceived <> BLANK(),
vAlreadyReceived,
vOrdered
)
Changed to:
RemainingQty =
VAR vAlreadyReceived =
LOOKUPVALUE(
Remaining[RemainingPurchaseQuantity],
Remaining[POandLine], PurchaseOrderLinesV2[POPlusLine],
0
)
VAR vOrdered =
MAX( PurchaseOrderLinesV2[OrderedPurchaseQuantity] )
RETURN
IF(
vAlreadyReceived <> 0,
vAlreadyReceived,
vOrdered
)
but that MAX() is giving the total for the whole column. Will put a second LOOKUPVALUE there methinks...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |