Forum Discussion
URGENT Help - Get distinct user count based on group by multiple columns
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?
PaulOldingSolution Sage
Hi Anonymous
I'm not at my computer so this is completely untested....
Users with Past Due Courses 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")
VAR _MinDue = CALCULATE(MIN(‘Table’[Due Date]), 'Table'[Status] <> "Completed")
RETURN
IF(_Courses = _Completed, BLANK(), IF(_MinDue < TODAY(), 1, BLANK()))
)
PaulOlding Unfortunately, this does not yield the right result
PaulOlding FYI, when a user completes a course there will be a new record that will be added with the "Date Occured" capturing the completion date. The previous record will still be residing in the dataset with a NotStarted status for that module (and courses) assigned to the user... basically, there is only append and no edits will happen to the dataset.
When I tested your expression above, I noticed the count is considering all the records with not completed status and those past due date, but when a user completed all courses in a module, that module for that user is still not filtered out.