Identifying staff who have not attended training

[GRWAM]

Hi all,

I'm trying to build a dashboard that will enable us to identify which staff are still to attend mandatory training. 

I have a table of current active staff and their job roles, a table of mandatory training required for each job role, and finally a table of all currently indate training undertaken by staff. 

My aim is to have a dashboard that can be filtered by training course to return a table listing all staff who are required to do this training but have yet to do so. 

I've got the relationships set up between the three table which enables me to create a filterable table showing those who in the relevant positions who have completed training. However, I'm at a loss about how to return a table showing those who have not completed the training.

Any advice on how I could create this list would be most appreciated! 

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks  for the solution @SamWiseOwl  provided, and i want to offer some more information for user to refer to.

hello @GRWAM , you can create the following measure.

Completed =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( 'Training History'[StaffID] ),
        'Training History'[Course Code]
            = SELECTEDVALUE ( 'Required Training'[Skill Code] ),
        'Training History'[StaffID] = SELECTEDVALUE ( Incumbents[StaffID] ),
        ALL ( 'Training History' )
    )
RETURN
    IF ( a > 0, "Completed" )

NotCompleted =
VAR a =
    VALUES ( Incumbents[Staff Name] )
RETURN
    IF (
        [Completed] = BLANK ()
            && SELECTEDVALUE ( Incumbents[Staff Name] ) IN a,
        "Not Completed"
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GRWAM]

Thanks for all your help on this! I've now managed to get this working as required!

[SamWiseOwl]

Very elegenat, nice job!

[SamWiseOwl]

Are you creating a visual or a data table i.e should this be a measure or calculated column?

[GRWAM]

Apologies - I should have been clearer in my initial message. 

I'm attempting to create a visual. My aim is to have a number of splicers which allow HR to filter staff by unit, job position and course. The end result would be a table visualisation which displays the members of staff who haven't attended the training of the specified course. 

[SamWiseOwl]

Hi @GRWAM 
It is hard to do without knowing the relationships or what is in the visual. My Gues would be something like:
Person Requires Training =
Var personId= SelectedValue(persontable[PersonId])

Return
If( Not( personId IN Values(completedtrainingtable[personId])), "Training needed", blank())

This captures the person whose row you are on in the table. Then checks if they are in the completedtraining table which is already being filtered by what your user choose in the slicer.

[GRWAM]

Thanks for this. Unfortunately, I can't seem to get PowerBi to accept this without a syntax error (I am adjusting field names etc.) 

My data tables look like this:

Incumbents:

Required Training:

Training History:

I have relationships set up between Incumbents (Position Number) > Required Training (Position Number), one to many, and Incumbents (StaffID) > Training History(StaffID), one to many.

Thank you for your advice so far, anything further would be greatly appreciated!

[SamWiseOwl]

Hi @GRWAM 

Test file here: Staff Training.pbix

Person Requires Training =

Var personId= SelectedValue(Incumbents[Position Number])

var trainingid = SelectedValue('Required Training'[Skill code])

var staffId = SelectedValue(Incumbents[StaffId])

var final =If( Not( staffId IN VALUES('Training History'[StaffID])) && personId IN values('Required Training'[Position Number]), "Training needed", blank())

Return

final

Person Completed Training =

Var personId= SelectedValue(Incumbents[Position Number])

var trainingid = SelectedValue('Required Training'[Skill code])

var staffId = SelectedValue(Incumbents[StaffId])

var final =If(  staffId IN VALUES('Training History'[StaffID]) && personId IN values('Required Training'[Position Number]), "Training Complete", blank())

Return

final

[GRWAM]

Thanks for all your help on this! I've now managed to get this working as required!

[SamWiseOwl]

Out of curiosity did mine work also?

[SamWiseOwl]

Hi @GRWAM 
You could create a column in the staff table like this:
If( StaffTable[StaffId] IN Values(TraingTable[StaffId]), "Training up to date", "Training required")

[GRWAM]

Hi @SamWiseOwl,

Unfortunately, I don't think this will work as each member of staff is required to do multiple courses so should appear in the training history table multiple times. 

Thanks for your advise though!