Forum Discussion
URGENT Help - Get distinct user count based on group by multiple columns
I have a requirement to get the count of distinct users who completed the modules. As you can see from the data in the table below, A module can have multiple courses and each course will be assigned to a user. The user then goes to the course detail page and can either set to in-progress or mark it as complete. The data occurred column will capture the event date of the user activity.
my requirement is to find all those unique users who completed all the courses that are part of a module and then it is considered as that user completed the module. In the below example, module 1 has 2 courses - course 1 & course 2; module 2 has course 1 & course 3.
Requirement: No of users who completed modules = 1
Reason: Only User A has completed all courses that are in Module 1
Things to note:
1) A course can be in multiple modules but when a user completes the course, then there will be multiple records created in the table, one for each course against the module for that user.
2) When a module is created and the courses are added to it, the user base be the same for all courses in that module.
| Module Name | Course Name | User Name | Status | Date occurred |
| Module 1 | Course 1 | A | Assigned | 10/10/2021 |
| Module 1 | Course 1 | B | Assigned | 10/10/2021 |
| Module 1 | Course 1 | C | Assigned | 10/10/2021 |
| Module 1 | Course 2 | A | Assigned | 10/10/2021 |
| Module 1 | Course 2 | B | Assigned | 10/10/2021 |
| Module 1 | Course 2 | C | Assigned | 10/10/2021 |
| Module 1 | Course 2 | A | Completed | 10/12/2021 |
| Module 2 | Course 3 | A | Assigned | 10/11/2021 |
| Module 2 | Course 3 | C | Assigned | 10/11/2021 |
| Module 2 | Course 1 | A | Assigned | 10/11/2021 |
| Module 2 | Course 1 | C | Assigned | 10/11/2021 |
| Module 2 | Course 1 | A | In Progress | 10/12/2021 |
| Module 2 | Course 1 | A | Completed | 10/12/2021 |
| Module 1 | Course 1 | A | Completed | 10/12/2021 |
Can someone pls share the DAX expression to use for the above requirement? Really appreciate it.
Hi Anonymous
Try this measure. It iterates the combination of modules and users and sees if the number of courses completed matches the total number of courses.
Users who Completed Modules Count = SUMX( SUMMARIZE('Table', 'Table'[Module Name], 'Table'[User Name]), VAR _Courses = CALCULATE(DISTINCTCOUNT('Table'[Course Name]), ALLEXCEPT('Table', 'Table'[Module Name])) VAR _Completed = CALCULATE(DISTINCTCOUNT('Table'[Course Name]), 'Table'[Status] = "Completed") RETURN IF(_Courses = _Completed, 1, 0) )
6 Replies
PaulOldingSolution Sage
Hi Anonymous
Try this measure. It iterates the combination of modules and users and sees if the number of courses completed matches the total number of courses.
Users who Completed Modules Count = SUMX( SUMMARIZE('Table', 'Table'[Module Name], 'Table'[User Name]), VAR _Courses = CALCULATE(DISTINCTCOUNT('Table'[Course Name]), ALLEXCEPT('Table', 'Table'[Module Name])) VAR _Completed = CALCULATE(DISTINCTCOUNT('Table'[Course Name]), 'Table'[Status] = "Completed") RETURN IF(_Courses = _Completed, 1, 0) )Hi PaulOlding ,
Thank you very much for sharing the expression, it works for my case.
Appreciate if you could assist for below additional step, I have another column "Due date" which will have a set date for each module, all courses within that module will have the same date by which the user assigned has to complete it. If a course is present in 2 different modules, the course due date could vary depending on the due date set on the module, whichever is the earliest due date takes precedence.
My requirement is to find all those users who have passed the due date and has not yet completed the module. Can you please provide an expression that could work for this situation ?
Module Name Course Name User Name Status Date occurred Due Date Module 1 Course 1 A Assigned 10/10/2021 10/20/2021 Module 1 Course 1 B Assigned 10/10/2021 10/20/2021 Module 1 Course 1 C Assigned 10/10/2021 10/20/2021 Module 1 Course 2 A Assigned 10/10/2021 10/20/2021 Module 1 Course 2 B Assigned 10/10/2021 10/20/2021 Module 1 Course 2 C Assigned 10/10/2021 10/20/2021 Module 1 Course 2 A Completed 10/12/2021 10/20/2021 Module 2 Course 3 A Assigned 10/11/2021 10/25/2021 Module 2 Course 3 C Assigned 10/11/2021 10/25/2021 Module 2 Course 1 A Assigned 10/11/2021 10/25/2021 Module 2 Course 1 C Assigned 10/11/2021 10/25/2021 Module 2 Course 1 A In Progress 10/12/2021 10/25/2021 Module 2 Course 1 A Completed 10/12/2021 10/25/2021 Module 1 Course 1 A Completed 10/12/2021 10/20/2021 Hi PaulOlding, Could you share an expression that could work in the above-mentioned scenario where a module is past due but not yet complete?