Hi All,
I have a table in which there are multiple entries of some students and what is the status of their subjects whether they have submitted the assignments or not.
And I have a dim table for all the subjects from which I can do DISTINCTCOUNT to check the total number of subjects/assignments. Now, I want to find out the number of students who have completed all the assignment.
Table looks like this:
| StudentName | Assignment | Status |
| s1 | assignment1 | completed |
| s2 | assignment1 | completed |
| s2 | assignment2 | pending |
| s3 | assignment1 | completed |
| s3 | assignment2 | completed |
Lets say that we only have two assignments in the dim table. Now, if we see the example above, only student3 have completed both the assignments. So, my final measure should give the result as 1 as only one student has completed all assignments. This is just an example subset of data.
The measure, that I am using is -
Students Completed =
VAR StudentwiseAssignmentSubmitted =
SUMMARIZE (
'Table',
'Table'[StudentName],
"SubmittedAssignmentCount",
CALCULATE (
DISTINCTCOUNT ( 'Table'[Assignment] ),
FILTER ( 'Table', 'Table'[Status] = "Completed" )
)
)
VAR TotalAssignments =
DISTINCTCOUNT ( dimAssignment[AssignmentName] )
RETURN
CALCULATE (
COUNTROWS ( StudentwiseAssignmentSubmitted ),
FILTER (
StudentwiseAssignmentSubmitted,
[SubmittedAssignmentCount] = TotalAssignments
)
)
But somehow this measure is always giving me total number of students present in the table. Tried various approaches but not getting the correct answer
Can anybody please help where it is going wrong?
Regards
