Number of unique students that completed all 10 courses
I have a little formula problem that I would really appreciate some help with.
The list has columns with Student names that repeat, Course names that repeat, and course status that can be passed, not passed, or not started.
I would like to count the number of unique students that passed all 10 courses that are available.
I tried different variations of Calculate and COUNTROWS.
This is the formula I have at the moment that doesn't work
PassedAll =CALCULATE(DISTINCTCOUNT(Progress[Student]),Progress[Mark]="Passed",Progress[Course]="Course1"&&Progress[Course]="Course2")
I understand that && doesn't work in this scenario because in a single row it cannot be both courses. And I don't want to replace it with an OR, || operator because I want to count students that have Passed marks on each of these courses.
Can someone please recommend how to somehow replace the course section of the filter with something that will include all 10 courses?
If you want only number to show in "Card Visualization" then:
StudentPassed = countrows(filter(GENERATE(VALUES(Sheet1[Student]), ROW("CoursCompleted", CALCULATE( DISTINCTCOUNT(Sheet1[Course]), Sheet1[Mark] ="Passed"))), [CoursCompleted]= 10))
in my sample data 1 Student Passed all, 1 Student Passed 9courses, 1 Student Pass 8 (and no record for 2 of course).
