none
PowerBI: DAX - Multi Table Data Validation RRS feed

  • General discussion

  • Hello,

    I have 2 tables with 3 columns of data on one and 2 on the other. I need to find a way to compare the data in 4 of those to get the right answer that lies within the 5th column based on the data available in each column that is reviewed.  How do I do this with DAX? In excel I just used the vLOOKUP option to do this.

    Old Formula: (EXCEL)

    =IFERROR(VLOOKUP(D2,'Home Team Owners'!B$2:C$65,2,0),(IFERROR(VLOOKUP(RIGHT(E2,LEN(E2)-10),'Home Team Owners'!$B$2:$C$65,2,0),(IFERROR(VLOOKUP(RIGHT(E2,LEN(E2)-4),'Home Team Owners'!$B$2:$C$65,2,0),(IFERROR(VLOOKUP(F2,'Home Team Owners'!B$2:C$65,2,0),"Unassigned")))))))

    Please help!

    Friday, April 6, 2018 5:45 PM

All replies

  • Current Attempt at DAX:

    Managers = 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]))) return if(HomeTeam1=BLANK(), if(HomeTeam2=BLANK(), if(assignment1 = BLANK(), if(assignment2 = BLANK(), "Unassigned", assignment2 ), assignment1 ), HomeTeam2 ), HomeTeam1 )

    Friday, April 6, 2018 7:54 PM