Forum Discussion
Solved
Find soonest data form second table
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 O...
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 Supplierand then repeat that for the other columns you want.
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.
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!