Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Patricia_LaVish
Frequent Visitor

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!

1 ACCEPTED SOLUTION

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

View solution in original post

12 REPLIES 12
tackytechtom
Super User
Super User

hi @Patricia_LaVish,

 

How about this:

tackytechtom_0-1704913312000.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Hi Tom!

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

Thanks!

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

Hi @Patricia_LaVish ,

 

Maybe this one for # compliant user?

Measure 3 = 
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
SUMX( _helpTable, [isCompleted])

 

Do not forget to mark the solution as an answer, if they solved your query 🙂

 

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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Hi @tackytechtom ,

I think I don't need to create VAR _allUsers as I'm not using it later,right?. Like what I did in my answer?. 

 

Any idea about why it's wrongfully counting non-compliant users?

 

Thanks!

 

hi @Patricia_LaVish ,

 

yes, you are right 🙂 I guess I was a bit too quick: If you wanna know only the ones that have completed all courses, then obviously you do not need to count all users.

 

For your question, the main formular "IF ( CALCULATE ( COUNTROWS ( 'Courses' ),  ALLEXCEPT ( 'Courses', 'Courses'[Email address] ),'Courses'[Completed] = "No" ) = 0, 1, 0 )" checks if a user had no "not-completed" courses (= the user completed all courses)

 

If you now use "IF(CALCULATE(COUNTROWS('Courses'),  ALLEXCEPT('Courses','Courses'[Email address]),'Courses'[Completed]="Yes") =0,1,0)", then you are checking whether a user had exactly 0 completed courses. This, however, will not give you the right result as, i.e. user1 had 2 completed and 2 uncompleted courses, meaning that user would fall under the radar.

 

In order to count the users that at least had one uncompleted course, you could count all user and substract all the ones that only had completed courses, like this one:

Measure 4 = 
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
_allUsers - SUMX( _helpTable, [isCompleted])

  

Hope this helps 🙂

 

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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Hi @tackytechtom 

Thanks for the quick answer!

I've tried it, and numbers are better now, but still wrong. Now I get 2774 compliant and 7442 not compliant, while the total number of users is 8285 😞

Corey_M
Resolver II
Resolver II

UsersCompletedAllCourses = 
COUNTROWS(
    FILTER(
        SUMMARIZE(
            YourTable,
            YourTable[user],
            "TotalCourses", CALCULATE(COUNTROWS(YourTable)),
            "CompletedCourses", CALCULATE(COUNTROWS(YourTable), YourTable[completed] = "Yes")
        ),
        [TotalCourses] = [CompletedCourses]
    )
)

Hi @Corey_M , 

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!

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

That worked like a charm, thank you!

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.