Hi, I am completely new to Power BI and just learned DAX from youtube videos so please bear with me.
I am working on two tables:
TABLE1 has columns A, B, C and D with the employee number, full name, role (trainer/agent), and the class number.
TABLE2 is the dump file for their assessment, kinda like how google form records each entry/answer individually. It has columns A, B, and C with the question number, employee number, and true/false respectively. Column C populates true/false to determine if they got the answer correctly or not.
I was able to establish a relationship between these two tables using the employee number from each, and now I am working on a dashboard that looks like this:
Trainer-----Class--Headcount--CompletionRate--AverageScore--NumberOfPassed
Trainer A---A1--------xxx---------------xxx---------------xxx---------------xxx-------
Trainer A---A2--------xxx---------------xxx---------------xxx---------------xxx-------
Trainer B---B1--------xxx---------------xxx---------------xxx---------------xxx-------
Trainer C---C1--------xxx---------------xxx---------------xxx---------------xxx-------
Got the Trainers and Class columns into the dashboard by simply dragging them from TABLE1, I also have the headcount by using counta for the employee number from TABLE1. After dragging the counta measure into the dashboard, the they were automatically filtered by the Trainer and Class columns. All good!
My problem now is getting the CompletionRate column to populate correctly: I tried using distinctcount for the employee number under TABLE2 because I though it would count the number of distinct values since people who did not take the test will not appear. This way, I will have a count of the test takers, then I will divide with the counta measure for the Headcount.
Unfortunately when I drag the disctinctcount measure into the dashboard, it shows the exact same value for all Trainers and Classes so I know I am getting an incorrect value for the CompletionRate.
Can anybody help me with this please? Why is distinct count showing the same values for all rows and does not filter the Trainer/Class like the Headcount column does? Should I use a different function or do I have to add something else?
If you can help me get the formula for the succeeding columns as well, that would be greatly appreciated.
Thanks!
