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
         )

     

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi sandierea,

 

In 'IS Service Request' table, please create a calculated column with below formula:

Manager =
VAR Manager =
    LOOKUPVALUE (
        'Home Team Owners'[Owner],
        'Home Team Owners'[Hometeam], 'IS Service Request  - Data List'[Home Team]
    )
VAR Manager2 =
    LOOKUPVALUE (
        'Home Team Owners'[Owner],
        'Home Team Owners'[Hometeam], 'IS Service Request  - Data List'[Team: Name]
    )
VAR Manager3 =
    LOOKUPVALUE (
        'Home Team Owners'[Owner],
        'Home Team Owners'[Hometeam], 'IS Service Request  - Data List'[Assignments]
    )
VAR Manager4 =
    LOOKUPVALUE (
        'Home Team Owners'[Owner],
        'Home Team Owners'[Hometeam], 'IS Service Request  - Data List'[Assignments_1]
    )
RETURN
    IF (
        Manager <> BLANK (),
        Manager,
        IF (
            Manager2 <> BLANK (),
            Manager2,
            IF (
                Manager3 <> BLANK (),
                Manager3,
                IF ( Manager4 <> BLANK (), Manager4, "Unassigned" )
            )
        )
    )

Best regards,

Yuliana Gu

  • sandierea's avatar
    sandierea
    Frequent Visitor
    8 years ago

    Thank you for your response.  I get the following error when using the formula:

     

    "A single value for column 'Home Team' in table IS Servcie Request - Data LIst' cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation, such as min, max, count or sum to get a single result"