Nested lookups

[sandierea]

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

[v-yulgu-msft]

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]

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"

[MFelix]

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]

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]

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.

[MFelix]

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]

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

[parry2k]

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

[parry2k · Accepted solution]

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
         )

[parry2k]

@sandierea

I thought I already provided you the link to the solution whcih seems not the case. Here is the link again, you can tweak as you see fit.

[parry2k]

@sandierea there are duplicate hometeam name in "Home Team Owners" table , I guess that is data issue, although "owner name" is different. team name is "Apps Finance"

[sandierea]

Yes, the duplicate will be removed for Apps Finance

[vijay_kolisetty]

Hi

I hope this site will help you, actually you can use the same if statement with the column name and condition(automatically it takes a range i.e., whole column)

https://community.powerbi.com/t5/Desktop/HOW-Calculated-Column-if-quot-this-column-quot-matches-quot...

Regards,

V