URGENT Help - Get distinct user count based on group by multiple columns

[Anonymous]

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.

[PaulOlding · Accepted solution]

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)
)

[Anonymous]

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

[Anonymous]

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()))

)

[Anonymous]

@PaulOlding Unfortunately, this does not yield the right result

[Anonymous]

@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.