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

[IanWaring]

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.

[IanWaring · Accepted solution]

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

[selimovd]

@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]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[IanWaring]

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