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 different columns and uses the first one found that matches a range of values and returns an owner name from that range.  If none are found, the formula returns "Unassigned".  How do I recreate this in PowerBI?

 

=IFERROR(VLOOKUP(D2,'Home Team Owners'!B$2:C$62,2,0),(IFERROR(VLOOKUP(RIGHT(E2,LEN(E2)-10),'Home Team Owners'!$B$2:$C$62,2,0),(IFERROR(VLOOKUP(RIGHT(E2,LEN(E2)-4),'Home Team Owners'!$B$2:$C$62,2,0),(IFERROR(VLOOKUP(F2,'Home Team Owners'!B$2:C$62,2,0),"Unassigned")))))))
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
         )

     

16 Replies

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

    You need to use the switch function to do this.

    Not on the computer right now but if you google it by DAX SWITCH or POWER BI SWTCH FUNCTION you will get several examples.

    Regards
    MFelix
    • 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.

  • 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"