Unable to get count of students who have submitted all assignments using DAX

Aksh_1234 — Tue, 30 Apr 2024 13:28:30 GMT

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

Re: Unable to get count of students who have submitted all assignments using DAX

HotChilli — Tue, 30 Apr 2024 15:43:38 GMT

Remove everything in the RETURN statement and replace it with :

COUNTROWS ( FILTER (StudentwiseAssignmentSubmitted, [SubmittedAssignmentCount] = _TotalAssignments) )

Re: Unable to get count of students who have submitted all assignments using DAX

Aksh_1234 — Wed, 01 May 2024 06:48:58 GMT

Hey, It worked. Thanks a lot. Could you please explain why it was not working earlier, because as per my understanding that is also the same thing.

topic Re: Unable to get count of students who have submitted all assignments using DAX in DAX Commands and Tips

Unable to get count of students who have submitted all assignments using DAX

Re: Unable to get count of students who have submitted all assignments using DAX

Re: Unable to get count of students who have submitted all assignments using DAX