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
         )

     

sandierea's avatar
sandierea
Frequent Visitor
8 years ago
Thank you, MFelix, If I understand the expression it seems that I would need to include every value from my original excel list in the DAX formula
parry2k's avatar
parry2k
Super User
8 years ago

you can do it by search function, use similar nested if statement or also can be done using relatinship. Share your sample data with all the columns and will get back to you with solution.

  • sandierea's avatar
    sandierea
    Frequent Visitor
    8 years ago

     

     

     

    • parry2k's avatar
      parry2k
      Super User
      8 years ago

      can you share the data with dropbox or google drive, i would like to recreate at my end give you the solution, 

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