Forum Discussion

sandierea's avatar
sandierea
Frequent Visitor
8 years ago
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...
Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    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
         )

     

MFelix's avatar
MFelix
Super User
8 years ago
Hi sandierea,

You want to search the team name (on both teams columns) or the owner (on assignemnts columns) and then return the owner name from the other table? Is this it or I'm not getting the question right?

Just to give you a better answer.

Regards,
MFelix
sandierea's avatar
sandierea
Frequent Visitor
8 years ago

Hi MFelix - I want to search both of the team name columns and both of the assignment columns, and if any of those four columns contain the home team name then the new manager column should return the owner name from the other table.

 

Thanks