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) )
Solution SageHi 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?
- PaulOlding
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