LOOKUPVALUE selecting the min value of a date

[AW1976NOVA]

Hi, 

I'm trying to do a LOOKUPVALUE formula, but I need to return only the lowest value.  But I can't get it to work (normal LOOKUPVALUE returns errors, given that it matches multiple values).

Background

I'm working with medical data, and I've got two tables of data.  Table 'Post Call to Home Append' and table 'Post Call Dates Append'

Problem

- I need to create a new column in the 'Post to Home Append' table that will pull in the earliest or min date of 'Post Call Dates Append'[PC Date].

WHERE

‘Post Call to Home Append’[Member ID]  = ‘Post Call Dates Append’[Mem ID]

And ‘Post Call to Home Append’[Discharge date] <= ‘Post Call Dates Append’[PC Date]

And ‘Post Call to Home Append’[Next Admit Date] >= ‘Post Call Dates Append’[PC Date]

Can you please assist?

[amitchandak · Accepted solution]

@AW1976NOVA , Try a new column like

minx( filter('Post Call Dates Append',
'Post Call to Home Append'[Member ID] = 'Post Call Dates Append'[Mem ID]
&& 'Post Call to Home Append'[Discharge date] <= 'Post Call Dates Append'[PC Date]
&& 'Post Call to Home Append'[Next Admit Date] >= 'Post Call Dates Append'[PC Date]),'Post Call Dates Append'[PC Date])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[AW1976NOVA]

Ahh, the minx function.  Thank you!