Forum Discussion
Solved
Count only compliant user
Hi, I need to count how many users have completed all courses. They're compliant only if ALL courses have completed, otherwise they're not. This is a data example. In this example, user1 should...
it seems like you figured it out, but the reason you were seeing it return 843, was because it was only counting those who had not completed any courses, Non-compliant users should include those who have completed courses, ie: any user whose number of completed course does not equal the number of courses assigned.
[TotalCourses] <> [CompletedCourses]So at the end of the measure instead of an =, you would use <> instead of switching Yes to No
Hi Tom!
I've tried, but for measure 1 I would need it to count compliant users, how could I do that?
Thanks!
Hi Tom,
I used this to count compliant users, which returns the right number, although tweaking it to show non-compliant is not returning the right number.
NEW_Compliant =
VAR _helpTable =
SUMMARIZE (
'Courses',
[Email address],
"isCompleted", IF ( CALCULATE ( COUNTROWS ( 'Courses' ), ALLEXCEPT ( 'Courses', 'Courses'[Email address] ),'Courses'[Completed] = "No" ) = 0, 1, 0 )
)
RETURN
SUMX( _helpTable, [isCompleted])
The formula to calculate the percentage also works fine!
For non-compliant I used this:
NEW_NOT_Compliant =
VAR _helpTable =
SUMMARIZE (
'Courses',
[Email address],
"isCompleted", IF ( CALCULATE ( COUNTROWS ( 'Courses' ), ALLEXCEPT ( 'Courses', 'Courses'[Email address] ),'Courses'[Completed] = "Yes" ) = 0, 1, 0 )
)
RETURN
SUMX( _helpTable, [isCompleted])