topic Re: Find soonest data form second table in DAX Commands and Tips

Find soonest data form second table

René — Thu, 13 Apr 2023 06:29:16 GMT

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é

Re: Find soonest data form second table

johnt75 — Thu, 13 Apr 2023 08:25:52 GMT

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.

Re: Find soonest data form second table

René — Thu, 13 Apr 2023 09:35:58 GMT

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!

Re: Find soonest data form second table

johnt75 — Thu, 13 Apr 2023 10:23:05 GMT

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.

Re: Find soonest data form second table

René — Thu, 13 Apr 2023 10:26:32 GMT

Marvelous, thanks!