Forum Discussion

rabih's avatar
rabih
Frequent Visitor
8 years ago
Solved

Lookupvalue returns only one value

I have to analyze one table Sales orders based on an older Sales table data. So for each record in the new table I have to find the closest delivered Sales order LastSO (primary key). I grouped the o...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi rabih,

     

    My mistake. :smileylol:

     

    Could you try the formula below to see if it works?

    ClosestSO =
LOOKUPVALUE (
    MaxRecIdByDateByCust[LastSO],
    MaxRecIdByDateByCust[Invoice account], NewOrders[Cust2Choose],
    MaxRecIdByDateByCust[Delivery date/time], CALCULATE (
        LASTDATE ( MaxRecIdByDateByCust[Delivery date/time] ),
        FILTER (
            ALL ( MaxRecIdByDateByCust ),
            ( MaxRecIdByDateByCust[Delivery date/time] ) <= NewOrders[DeliveryDate]
                && MaxRecIdByDateByCust[Invoice account] = NewOrders[Cust2Choose]
        )
    )
)

     

    Regards

rabih's avatar
rabih
Frequent Visitor
8 years ago

I have copied and pasted your code snippet.

Got following error:

Something's wrong with one or more fields:(NewOrders) ClosestSO:

A table expression containing more than one column was specified in the call to function 'LASTDATE'. This is not supported.

 

 

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi rabih,

 

My mistake. :smileylol:

 

Could you try the formula below to see if it works?

ClosestSO =
LOOKUPVALUE (
    MaxRecIdByDateByCust[LastSO],
    MaxRecIdByDateByCust[Invoice account], NewOrders[Cust2Choose],
    MaxRecIdByDateByCust[Delivery date/time], CALCULATE (
        LASTDATE ( MaxRecIdByDateByCust[Delivery date/time] ),
        FILTER (
            ALL ( MaxRecIdByDateByCust ),
            ( MaxRecIdByDateByCust[Delivery date/time] ) <= NewOrders[DeliveryDate]
                && MaxRecIdByDateByCust[Invoice account] = NewOrders[Cust2Choose]
        )
    )
)

 

Regards

  • rabih's avatar
    rabih
    Frequent Visitor
    8 years ago

    You nailed it :)