Lookup max value in another column

[alagator28]

Hello,

I have two tables shown below. I am looking to fill the "Eligible" column of Table1. There is a many to many relationship between the tables. I've been trying to accomplish this using the Lookupvalue function, but I have not been successful. Here are the requirements:

For each row in Table 1:

• Check ALL rows in Table2 matching the Employee found in Table 1
• If any rows in Table2 have a date which is equal to or greater than the row in Table1, return "Yes"
• Otherwise, return "No"

Table1

Employee	Date	Eligible
John Smith	12/25/2023	Yes
John Smith	12/28/2023	No

Table2

Employee	Date
John Smith	12/21/2023
John Smith	12/24/2023
John Smith	12/25/2023

Any ideas?

Thanks in advance.

[Greg_Deckler]

@alagator28 Try this:

Eligible Column in Table1 =
  VAR __Employee = 'Table1'[Employee]
  VAR __Date = 'Table1'[Date]
  VAR __Table = FILTER('Table2', [Employee] = __Employee && [Date] >= __Date)
  VAR __Return = IF( __Table = BLANK(), "No", "Yes")
RETURN
  __Return

[alagator28]

Thank you @Greg_Deckler . Unfortunately, I'm getting the following error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

To troubleshoot this, I entered real values in the variables for __Employee and __Date and I still get the error. It seems to be coming from the __Table variable. I've made sure that the date format is exactly the same, as well as the matching Employee.

Any clues on what I should check? Thanks.