Forum Discussion
Solved
Nested lookups
Hello, I have an Excel spreadsheet where I use the following formula to populate a column of cells based on values that could be in different columns. The formula looks for a value in three differen...
I did that just for checking what is returning the value, change the "manager" measure highlighted red as mentioned below and it will do it.
Manager = var HomeTeam1 =CALCULATE(FIRSTNONBLANK('Home Team Owners New'[Owner],1), Filter('Home Team Owners New', 'Home Team Owners New'[Hometeam] = MAX('IS Service Request - Data List'[Home Team])) ) var HomeTeam2 =CALCULATE(FIRSTNONBLANK('Home Team Owners New'[Owner],1), USERELATIONSHIP('IS Service Request - Data List'[Team: Name], 'Home Team Owners New'[Hometeam]), Filter('Home Team Owners New', 'Home Team Owners New'[Hometeam] = MAX('IS Service Request - Data List'[Team: Name])) ) var assignment1 = CALCULATE(FIRSTNONBLANK('Home Team Owners New'[Owner],1), USERELATIONSHIP('IS Service Request - Data List'[Assignments], 'Home Team Owners New'[Hometeam]), FILTER('Home Team Owners New', 'Home Team Owners New'[Hometeam] = MAX('IS Service Request - Data List'[Assignments]))) var assignment2 = CALCULATE(FIRSTNONBLANK('Home Team Owners New'[Owner],1), USERELATIONSHIP('IS Service Request - Data List'[Assignments_1], 'Home Team Owners New'[Hometeam]), FILTER('Home Team Owners New', 'Home Team Owners New'[Hometeam] = MAX('IS Service Request - Data List'[Assignments_1]))) return if(HomeTeam1=BLANK(), if(HomeTeam2=BLANK(), if(assignment1 = BLANK(), if(assignment2 = BLANK(), "Unassigned", assignment2 ), assignment1 ), HomeTeam2 ), HomeTeam1 )
