Forum Discussion

Patricia_LaVish's avatar
Patricia_LaVish
Frequent Visitor
2 years ago
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 NOT be compliant, but user 2 should be counted as compliant. I also need to calculate the percentage of compliant users.

CourseUserCompleted
Course 1

User1

Yes
Course 2User1No
Course 3User1Yes
Course 4User1No
Course 1User2Yes
Course 2User2Yes
Course 3User2Yes
Course 4User2Yes

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago
    UsersCompletedAllCourses = 
COUNTROWS(
    FILTER(
        SUMMARIZE(
            YourTable,
            YourTable[user],
            "TotalCourses", CALCULATE(COUNTROWS(YourTable)),
            "CompletedCourses", CALCULATE(COUNTROWS(YourTable), YourTable[completed] = "Yes")
        ),
        [TotalCourses] = [CompletedCourses]
    )
)
    • Patricia_LaVish's avatar
      Patricia_LaVish
      Frequent Visitor
      2 years ago

      Hi Anonymous , 

      I've tried it, but the number it returns is not right. I have a total of 8285 users but using your answer shows 2774 compliant users. If I tweak it to show non compliant users using YourTable[Completed]="No", it returns 843 noncompliantusers, which in total are way less than 8285 😞

      Thanks!

      • Patricia_LaVish's avatar
        Patricia_LaVish
        Frequent Visitor
        2 years ago

        Asnwering myself: The number of compliant users is right, but for some reason the number of non-compliant is not. I used this:

        Not_Compliant =
        COUNTROWS(
            FILTER(
                SUMMARIZE(
                    Courses,
                    Courses[Email address],
                    "TotalCourses", CALCULATE(COUNTROWS(Courses)),
                    "CompletedCourses", CALCULATE(COUNTROWS(Courses), Courses[Completed] = "No")
                ),
                [TotalCourses] = [CompletedCourses]
            )
        )
  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    2 years ago

    hi Patricia_LaVish,

     

    How about this:

     

    Here the two measures:

    Measure 1 = 
IF ( 
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Completed] = "No" ) = 0,
    "complete",
    "incomplete"
)

     

    Measure 2 = 
VAR _helpTable =
SUMMARIZE (
    'Table',
    [User],
    "isCompleted", IF ( CALCULATE ( COUNTROWS ( 'Table' ),  ALLEXCEPT ( 'Table', 'Table'[User] ),'Table'[Completed] = "No" ) = 0, 1, 0 )
)
VAR _allUsers = DISTINCTCOUNT ( 'Table'[User] ) 
RETURN
DIVIDE ( SUMX( _helpTable, [isCompleted]), _allUsers )

     

    Let me know how it works 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

    • Patricia_LaVish's avatar
      Patricia_LaVish
      Frequent Visitor
      2 years ago

      Hi Tom!

      I've tried, but for measure 1 I would need it to count compliant users, how could I do that?

      Thanks!

      • Patricia_LaVish's avatar
        Patricia_LaVish
        Frequent Visitor
        2 years ago

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