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.
QA Rank =
RANKX(
ALL(QA[Agent1]),
QA[Eval Score %],,
DESC,
Skip
)
Also, is there a way I could add a filter to these rank calculations to only rank based on other columns from the Headcount report?
Thank you!
