Help with metric

[Stubby]

Hi All 

 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

[philouduv]

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,

[Stubby]

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.

[philouduv · 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,

[Stubby]

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