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 old Sales order in a table based on Customer Date and Maximum RecId for each date. There are no duplicate Deliver date/time and LastSO records. When I created a column ClosestSO as follows:

 

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

 

I only get one record. Any idea why. I used the same logic for Exchange rates and it worked well

  • 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

4 Replies

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

    Hi rabih,

     

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

    ClosestSO =
LOOKUPVALUE (
    MaxRecIdByDateByCust[LastSO],
    MaxRecIdByDateByCust[Invoice account], NewOrders[Cust2Choose],
    MaxRecIdByDateByCust[Delivery date/time], LASTDATE (
        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