cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IanWaring
Helper III
Helper III

LOOKUPVALUE - on lookup failure, how to use value from another field in current row?

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:

RemainingQty = LOOKUPVALUE(
  Remaining[RemainingPurchaseQuantity],
  Remaining[POandLine],
  PurchaseOrderLinesV2[POPlusLine],
0)

That works fine for orders that have started having items received against them. However, orders yet to receive any shipment do not exist in the Remaining table, so in those cases, i'd like to set RemainingQty as a copy of what's in PurchaseOrderLinesV2[OrderedPurchaseQuantity] in the same record. However:

RemainingQty = LOOKUPVALUE(
  Remaining[RemainingPurchaseQuantity],
  Remaining[POandLine],
  PurchaseOrderLinesV2[POPlusLine],
RELATED(PurchaseOrderLinesV2[OrderedPurchaseQuantity]))

claims no relation in place.

RemainingQty = LOOKUPVALUE(
  Remaining[RemainingPurchaseQuantity],
  Remaining[POandLine],
  PurchaseOrderLinesV2[POPlusLine],
MAX(PurchaseOrderLinesV2[OrderedPurchaseQuantity]))

gives every record the same maximum total, and likewise when I try SUM.

Any ideas how I update each record with it's own PurchaseOrderlLinesV2[RemainingQty] = PurchaseOrderLinesV2[OrderedPurchaseQuantity] if the lookup fails?

Any help/guidance/pointers gratefully received.
1 ACCEPTED SOLUTION
IanWaring
Helper III
Helper III

Thank you - I managed to nest a second LOOKUPVALUE and job done - all working. Thank you.

View solution in original post

4 REPLIES 4
IanWaring
Helper III
Helper III

Thank you - I managed to nest a second LOOKUPVALUE and job done - all working. Thank you.

View solution in original post

selimovd
Community Champion
Community Champion

@IanWaring 

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

selimovd
Community Champion
Community Champion

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
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

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...

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors