cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stubby
Helper I
Helper I

Help with metric

Hi All 

 

Stubby_1-1651135422729.png 123rew

I have a training dataset that has just under 30,000 staff. In that data set it records a Curriculum that they have to follow and each curriculum has items that need to be studied (the amount of items varies depending on the curriculum). Once the item has been studied the system is marked as Complete. For a member of staff to be fully compliant ALL items must have a Complete status.

 

What I am struggling to create is a measure that will be used in a table that will show the member of staff as Not Compliant if any of the items for each curriculum states incomplete. For example using the image above the member of staff would be recorded as Fully Compliant for CORP-CCO-COBC-Foundations as all items are complete under comstatus. If one of the items is Incomplete I need to record Not Compliant.

 

Is this possible? Any help would be much appreciated

1 ACCEPTED SOLUTION

Hey @Stubby ,

In my solution, this work at the user_id level meaning that no matter which modules from whichever curriculum he has no complete it will be not compliant.

If youwant to be able to filter also at the module level you could add 

Nbr_non_complete = COUNTX(
Filter(datasource,datasource[USER_ID] == Earlier(datasource[USER_ID],
datasource[Curriculum_ID == Earlier(datasource[Curriculum_ID])
&&
datasource[compStatus] == "no"),
datasource[compStatus])

Which will now give the count for each curriculum and User.

Best regards,

View solution in original post

4 REPLIES 4
philouduv
Resolver II
Resolver II

Hey @Stubby,

You could count for each User the nbr of Non-complete status and if they got at leat 1 they should be Non-compliant it yould be something like this:

Nbr_non_complete = COUNTX(
Filter(datasource,datasource[USER_ID] == Earlier(datasource[USER_ID])
&&
datasource[compStatus] == "no"),
datasource[compStatus])

Best regards,

Thank you @philouduv 

 

I will give that a try. The staff do have multiple curriculum that they have to complete. In my example I just displayed one curriculum. How would I apply this same principle to each Curriculum the staff are assigned.

Hey @Stubby ,

In my solution, this work at the user_id level meaning that no matter which modules from whichever curriculum he has no complete it will be not compliant.

If youwant to be able to filter also at the module level you could add 

Nbr_non_complete = COUNTX(
Filter(datasource,datasource[USER_ID] == Earlier(datasource[USER_ID],
datasource[Curriculum_ID == Earlier(datasource[Curriculum_ID])
&&
datasource[compStatus] == "no"),
datasource[compStatus])

Which will now give the count for each curriculum and User.

Best regards,

Fantastic, I shall try this out later. Many thanks for your help

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.