Forum Discussion
How to use RANKX across multiple tables
Hello!
I have 4 tables full of user data connected by a user table called Headcount. It connects by several foreign keys to the four different tables where I'm already using rankx to calculate ranks and scores to rank them on. Below is an example of the tables for reference:
Headcount
| Agent1 | AgentID | ADID |
| Fred | 2321315 | FFED1 |
| Chuck | 5555432 | CHUCKY1 |
| Larry | 3333333 | LARRY9 |
| Paul | 2212345 | PAUL89 |
`Headcount`[ADID] > `QA`[AgentID]
1) QA
| Agent1 | AgentID | Eval Score % |
| Fred | Freddy | 65% |
| Chuck | Chucky1 | 85% |
| Larry | Larry | 90% |
| Paul | Paully | 75% |
`Headcount`[Agent1] > `Surveys`[Agent1]
2) Surveys
| Agent1 | AgentID | Score |
| Fred | Freddy | 65% |
| Chuck | Chucky1 | 85% |
| Larry | Larry | 90% |
| Paul | Paully | 75% |
`Headcount`[Agent1] > `ADH`[Agent1]
3) ADH
| Agent1 | AgentID | ADH % |
| Fred | Freddy | 65% |
| Chuck | Chucky1 | 85% |
| Larry | Larry | 90% |
| Paul | Paully | 75% |
`Headcount`[Agent1] > `AHT`[AHT %]
4) AHT
| Agent1 | AgentID | AHT % |
| Fred | Freddy | 65% |
| Chuck | Chucky1 | 85% |
| Larry | Larry | 90% |
| Paul | Paully | 75% |
I'm performing multiple ranks across all these tables that all tie back to the headcount table. How do I build the dax in a way that will still utilize my relationships in my datamodel without referring to the individual user identifying field in each table? Below is one of my current rank formulas for reference.