select value based on max in ssrs

Question

In the table show below, based on maximum lead time the Order Quantity should select, and Order qty should not be zero. if zero means need to select next value based on next highest value of lead time.

In this example, In lead time max value is 12. based on lead time order qty is 0, so the next highest lead time is 11 final answer to select is 5.

Present I'm using this code. it returning first appearing value. Need to apply above logic.

=LookupSet(Fields!InvoiceNumber.Value & Fields!PONumber.Value, Fields!InvoiceNumber.Value & Fields!PONumber.Value,Fields!CustomerOrderQty.Value, "dsILI")(0)

my Query.

SELECT mmm_mmmidno, mmm_productdesc, mmm_invoiceno AS InvoiceNumber, mmm_pono AS PONumber, mmm_billunit AS BillUnit,mmm_customerorderedqty AS CustomerOrderQty,mmm_itemleadtime as ItemLeadTime FROM Filteredmmm_invoicelineiteminfo

Data:

   Invoice No   Lead Time   Order Qty
   -----------------------------------
   ET2010           2         10
   ET2010           5         22
   ET2010           8          4
   ET2010           4          7
   ET2010           6          8
   ET2010          12          0
   ET2010          11          5
   ET2010           9          3

Answer 1

Just change your query like so:

SELECT mmm_mmmidno
      ,mmm_productdesc
      ,mmm_invoiceno AS InvoiceNumber
      ,mmm_pono AS PONumber
      ,mmm_billunit AS BillUnit
      ,mmm_customerorderedqty AS CustomerOrderQty
      ,mmm_itemleadtime as ItemLeadTime 
FROM Filteredmmm_invoicelineiteminfo
WHERE mmm_customerorderedqty > 0
ORDER BY ItemLeadTime DESC