Forum Discussion

alagator28's avatar
alagator28
Icon for Helper II rankHelper II
2 years ago

Lookup 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 ...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
    alagator28
    Icon for Helper II rankHelper II
    2 years ago

    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.