Forum Discussion
Helper IILookup max value in another column
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.
2 Replies
- Greg_Deckler
Community Champion
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
alagator28Thank 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.
