cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Calculating percentage compliance for annual courses

Hello

I have a curated data set with a table named Employee Training, and a Course Completed Date. Some of the courses are required to be completed annually so many staff have completed these courses numerous times. I have a separate table for Course and Frequency which is a lookup table for the course name and frequency.

I have been able to create measures to extract the latest course completion, and one to return only those who have been completed in the last 365 days.

Where I am having problems is to calculate a percentage compliance for the annual courses. If a person has completed the course more than once it counts each completion into the sum of the courses completed.

I am unable to share the pbix file for organisational reasons. So I am hoping a table will help illustrate the issue. In the table containing 5 people below, the compliance should be 100% as they have all completed the annual course within the past year.

 Course template name Emp ID Emp First Emp Last Course Completed Calendar Date Code Orange Evac 105958 Mary Smith 1/12/2022 Code Orange Evac 106892 John Jamieson 1/12/2022 Code Orange Evac 23231 Una Brown 1/12/2022 Code Orange Evac 40339 Becca Jones 1/12/2022 Code Orange Evac 191775 Fred White 28/11/2022 Code Orange Evac 191775 Fred White 9/12/2022 Code Orange Evac 191775 Fred White 7/02/2023

When I use the DAX measure below for courses only completed once the correct result is returned. The same formula used for the above returns 140%

% Comp = DIVIDE([Count Employee Training],'Employee Training Group'[Count Employee Training Group])

Even using Distinctcountnoblank it continues to count all course completions. I have found other DAX measures to display the compliant dates for annual courses in a table, but this % compliance is just not working.

Can someone suggest a formula that will remove all but 1 date that will refer to the Employee Training [course completed date]?
Happy to provide further information.

As I have to put a formula into a matrix with the annual and once only courses I would also need a formula to refer to both ideally.
Thank you
Helen
5 REPLIES 5
Super User

You'll need a column which uniquely identifies each row. If you don't have one then you can use Power Query to add an index column. In the modelling view mark that column as the key for the table.

You can then get the number of courses completed in the past year with something like

``````Courses completed this year =
VAR LastYear =
DATE ( YEAR ( TODAY () - 1 ), MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR SummaryTable =
INDEX (
1,
'Table',
ORDERBY ( 'Table'[Completion date], DESC ),
PARTITIONBY ( 'Table'[Employee ID] )
)
VAR NumCoursesCompleted =
COUNTROWS ( FILTER ( SummaryTable, 'Table'[Completion date] > LastYear ) )
RETURN
NumCoursesCompleted
``````

If you are going to have lots of measures looking at the most recent completion then it might be worth creating a calculated table using the INDEX function from the above code, and your measures could then refer to that.

Frequent Visitor

Hi John

Thanks that makes sense. When I try to create an index column or table it is throwing an error. This is the code:

Courses this year index =
INDEX(1, 'Employee Training', ORDERBY( 'Employee Training'[Course Completed Date], DESC), KEEP, PARTITIONBY('Employee Training'[Employee He Number]))

The error is INDEX's Relation paramet may have duplicate rows. This is not allowed.

Can you suggest a fix for that?
Thank you
Helen
Super User

You'll need a column which uniquely identifies each row. If you don't have one then you can use Power Query to add an index column. In the modelling view mark that column as the key for the table.

Frequent Visitor

Hi Johnt75

I tried to do that but the Employee table is a curated data set and I can't view the table in Power Query.

I have tried making a calculated column but can't get the syntax right.

Thanks anyway. Let me know if you have any other ideas, or if someone out there has any sugguestions.

Regards

Helen

Super User

Try

``````Courses completed this year =
VAR LastYear =
DATE ( YEAR ( TODAY () - 1 ), MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR SummaryTable =
SUMMARIZE ( 'Table', 'Table'[Course template name], 'Table'[Employee ID] ),
"@last completed", CALCULATE ( MAX ( 'Table'[Completion date] ) )
)
VAR NumCoursesCompleted =
COUNTROWS ( FILTER ( SummaryTable, [@last completed] > LastYear ) )
RETURN
NumCoursesCompleted
``````

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors