Find soonest data form second table

[René]

Dear fellow BI colleagues,

I am struggling to get the following to work, as shown in the image below. My intention is to list the date, supplier name, and quantity to be received for each Sales Order Line, for the next upcoming receipt. I have tried various functions, and have been able to lookup the next receipt date using the MIN() function, but I'm unsure how to include the supplier name and quantity. Could you please help me with this?

So for example on the second Sales Order Line (product: 2934EV16K) I want to see that the next inbound will be: 2000 pce of supplier C on 15-4-2023.

Thank you very much in advance for your assistance.

Best regards,

René

[johnt75 · Accepted solution]

You could create a measure like

Next expected date =
VAR CurrentProduct =
    SELECTEDVALUE ( 'Sales Order'[Product] )
VAR Supplier =
    MINX (
        TOPN (
            1,
            FILTER (
                'PO Lines',
                'PO Lines'[Product] = CurrentProduct
                    && 'PO Lines'[Expected date] >= TODAY ()
            ),
            'PO Lines'[Expected date], ASC
        ),
        'PO Lines'[Supplier]
    )
RETURN
    Supplier

and then repeat that for the other columns you want.

[René]

Great! Thanks for the swift reply.

That does the trick, but what it also does is by adding this measure to the sales order line table, it then shows only the rows with a linked PO line. Could you please also help me out on this? Howto show all the Sales Order Lines, regardless if there is an open PO line for?

Thanks again!

[johnt75]

If you click on the sales order number in the fields well of the visual there should be an option to "show items with no data", I think that should do it.

[René]

Marvelous, thanks!