Forum Discussion
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...
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 )
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
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
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
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.