topic Re: Lookup max value in another column in DAX Commands and Tips

Lookup max value in another column

alagator28 — Wed, 15 Nov 2023 15:10:14 GMT

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.

Re: Lookup max value in another column

Greg_Deckler — Wed, 15 Nov 2023 15:23:28 GMT

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

Re: Lookup max value in another column

alagator28 — Wed, 15 Nov 2023 15:43:28 GMT

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.