Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table imported from Excel that is structured with the following structure below (important fields listed below).
The background: We have a student ID who is tied to 1 or more programs. Each program could have 1 or more exams associated (identified as "proficiency" in the item type). And a score of 80 or higher is passing while a blank score or less than 80 is not completed. The "Completed" field also indicates whether or not a user has passed the exam.
User ID | Program | Exam | Score | Completed | Item Type |
WT | Prog 1 | Test 1 | 0 | No | Proficiency |
WT | Prog 1 | Test 2 | 100 | Yes | Proficiency |
WT | Prog 1 | Test 3 | 80 | Yes | Proficiency |
WT | Prog 1 | Test 4 | 80 | Yes | Proficiency |
WT | Prog 1 | Test 5 | 100 | Yes | Proficiency |
WT | Prog 2 | Test 6 | 80 | Yes | Proficiency |
WT | Prog 1 | Test 7 | Proficiency | ||
WT | Prog 2 | Test 8 | 80 | Yes | Proficiency |
SH | Prog 2 | Class | |||
SH | Prog 2 | Test 1 | 100 | Yes | Proficiency |
I have two measures to try and properly count completion percentages: 1. i need to count users who have completed all of the exams associated to their program and 2. i need to count all users who have completed all exams across all of their programs.
So as an example, user WT is assigned to both prog 1 an prog 2.
In prog 1, he has only completed 4 of the 6 exams assigned to prog 1, and therefore would return a 0 towards the total count of users who completed all their exams for prog 1.
For prog 2, WT completed the 2 exams assigned to prog 2, and therefore should return as 1 user who completed all of his exams.
In the 2nd measure Ive been working towards, WT did not complete all exams assigned to both of his programs (prog 1 and prog 2) and therefore that measure would return as 0 users who completed all of his exams for all programs.
User SH is only assigned to Prog 2 but has completed his 1 exam (proficiency), so therefore he should also count towards both measures (completed all requirements for his program and completed all measures for all programs).
In my attempt so far, I am wondering if i have to create a custom column in order to properly count the user assigned exams or if this can be done more elegantly with measures. If this wordy description requires clarity, let me know.
Thanks!
Solved! Go to Solution.
Hi @sedric1 ,
I suggest you create a summarize table and :
Table2 = SUMMARIZE('Table','Table'[Program],'Table'[User ID],"Pass",IF(CALCULATE(COUNT('Table'[Exam]),FILTER('Table','Table'[Score]<80&&'Table'[Item Type] = "Proficiency"))>0,"NO","YES"))
PASSALL = IF("NO" in CALCULATETABLE(VALUES('Table2'[Pass]), ALLEXCEPT(Table2,Table2[User ID])),0,1)
Then you can use the following two measure :
Measure1 = COUNTX(FILTER(Table2,Table2[Pass] = "YES"),'Table2'[User ID])+0
Measure2 = COUNTX(FILTER(Table2,Table2[PASSALL] = 1),Table2[User ID])
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWES67XnUUBLmqExJg...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @sedric1 ,
I suggest you create a summarize table and :
Table2 = SUMMARIZE('Table','Table'[Program],'Table'[User ID],"Pass",IF(CALCULATE(COUNT('Table'[Exam]),FILTER('Table','Table'[Score]<80&&'Table'[Item Type] = "Proficiency"))>0,"NO","YES"))
PASSALL = IF("NO" in CALCULATETABLE(VALUES('Table2'[Pass]), ALLEXCEPT(Table2,Table2[User ID])),0,1)
Then you can use the following two measure :
Measure1 = COUNTX(FILTER(Table2,Table2[Pass] = "YES"),'Table2'[User ID])+0
Measure2 = COUNTX(FILTER(Table2,Table2[PASSALL] = 1),Table2[User ID])
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWES67XnUUBLmqExJg...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@v-deddai1-msft To followup on this, I did some tweaking to the logic per my full data file and got it to work using your method. Thanks again for the help as I was totally stuck without you.
@v-deddai1-msft Thanks so much for the reply!
It did not work for me once I applied the summarized table, column and measures to my entire file. I do think I am closer with the summarized table but it is counting too many user IDs (measure 2) so I am trying to see if maybe its not pulling in distinct values or something else now.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |