Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sedric1
Helper III
Helper III

Need help creating counting measures per criteria

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 IDProgramExamScoreCompletedItem Type
WTProg 1Test 10NoProficiency
WTProg 1Test 2100YesProficiency
WTProg 1Test 380YesProficiency
WTProg 1Test 480YesProficiency
WTProg 1Test 5100YesProficiency
WTProg 2Test 6 80 YesProficiency
WTProg 1Test 7  Proficiency
WTProg 2Test 8 80 YesProficiency
SHProg 2   Class
SHProg 2Test 1 100YesProficiency

 

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!

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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)

 

Capture2.PNG

 

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])

 

Capture3.PNG

 

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

 

View solution in original post

3 REPLIES 3
v-deddai1-msft
Community Support
Community Support

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)

 

Capture2.PNG

 

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])

 

Capture3.PNG

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.