For each user, unique latest date occurence of any of a subset items -linked table,multiple criteria

[patrick_d]

Hi

I am new to PowerBI and despite trying out various approaches based on other threads (eg. @v-diye-msft , @PaulDBrown) , I have not managed to find a working solution to the following:

I need to identify staff who need to attend refresher training based on knowing the most recent date they have completed relevant training.

Relevant training is a subset of possible training ie. only 3 modules qualify - let's call them:
training_A_relevant
training_B_relevant
training_C_relevant

So the logic I am looking for is:
For each staff_name, what is the most recent date that any RELEVANT training session has been attended. (I can then use DATEDIFF to find the number of days between TODAY and that date, to identify who needs refresher training and show compliance dashboard).

Sample data is as follows:

StaffTable:
staff_name
Lee
Jane
Chen

TrainingTable:
staff_name  training_module             training_date
Lee              training_A_relevant         10 Oct 2018
Lee              training_B_relevant          30 Apr 2018
Lee              training_D_not_relevant   05 Jul 2019
Jane            training_C_relevant          11 Feb 2019
Jane            training_D_not_relevant  11 Nov 2019
Jane            training_A_relevant         10 Oct 2018
Chen           training_D_not_relevant  10 Oct 2018

The result I am after is to add two new columns to the StaffTable to provide:
1. LatestRelevantTrainingDate for each staff_name showing the most recent date any relevant training occured (this is the most important criteria) and
2. training_module  the name of the relevant training module done at that LatestRelevantTrainingDate.

So based on the sample data above, the result I am after is the following:

StaffTable: (Updated)
staff_name   LatestRelevantTrainingDate   training_module
Lee               10 Oct 2018                           training_A_relevant
Jane             11 Feb 2019                           training_C_relevant
Chen            NULL                                      NULL

Many thanks for your help.

[PaulDBrown]

@patrick_d 

The first thing I have done is create lookup tables for the main fields; date, courses and staff. I also included a column in the main fact table which identifies whether a course is defined as "relevant" or "not relevant" with:

Relevance = IF(CONTAINSSTRING(TrainingTable[training_module]; "not_relevant"); "Not Relevant"; "Relevant")

The model looks like this:

Then create the measures:

1) to calculate the last date for relevant courses per staff member:

Last Date by Relevant Course = CALCULATE(MAX(TrainingTable[training_date]); 
ALLEXCEPT(StaffTable; StaffTable[staff_name]); 
FILTER(TrainingTable;
TrainingTable[Relevance] = "Relevant"))

2) to calculate the last date for all dates and courses (by Staff member):

Last Relevant date by Staff member = CALCULATE([Last Date by Relevant Course]; 
    ALLEXCEPT(StaffTable; StaffTable[staff_name]); 
        ALL('Training Code'); ALL('Calendar'[Date]))

3) Next calculate the relavant course on this last date:

Relevant Training Course = 
CALCULATE(MAX('TrainingTable'[Training Course]); 
    FILTER('Calendar';
        'Calendar'[Date] = [Last Relevant date by Staff member]))

4) If you want to include Staff Members who have no relevant courses ("Chen" in your example), use these two measures:

Last Rel. Training Course (For Table) = IF(
    ISBLANK([Last Relevant date by Staff member]); 
    "Null"; 
        [Relevant Training Course])

Last Rel. Training Date (For Table) = IF(
    ISBLANK([Last Relevant date by Staff member]); 
        "Null"; 
            [Last Relevant date by Staff member])

And with all this, you get the following:

Here is the PBIX file for your reference:
Staff Training 

[patrick_d]

Many thanks for your prompt replies Ken @kentyler and @PaulDBrown  - much appreciated.

Please note that the data/tables provided were a simplification to highlight what I was trying to solve.

The actual data set is based on a Dynamics CRM connection,so the Staff table is in fact a contacts table with a unique contactid and countless columns; there is a separate training_table with module_ID and name where I have created the equivalent of a "Relevant" column, a training_sessions table that has the training date with the link back to the module_ID in the training_table and link back to the contactid in the contacts table.

Just a couple of notes before sending kudos your way in case it helps others:
- Ken, I had previously used the logic you provided but was not getting the expected unique result for Last Relevant Date. I realised this was because the automatic relationships created by PowerBI when I imported the data from Dynamics was not a uni directional One->Many and so adjusting this gave the desired result for Last Relevant Date.
- On the other hand, just using that formula Ken does not give me a single entry per staff showing the latest relevant module that matches that last date. Instead for every matched staff member the table lists all the other training done by that staff member as well.
- Paul, your more comprehensive approach looks like it might address this once I have had a chance to do the necessary changes. Note though that it is common for staff to take part in two training sessions on the same date.

Thanks again

[PaulDBrown · Accepted solution]

@patrick_d 

You can set up the model to show more than one course in a single day for staff member:

If you can have more than 1 relevant course in a single day, you can create measures as a filter:

Countrows training courses = 
COUNTROWS(CALCULATETABLE(VALUES(TrainingTable[Training Course]); 
    FILTER('Calendar'; 
    'Calendar'[Date] = [Last Relevant date by Staff member])))

And use this in the filter pane;

Unfortunately this method will not allow you to see those without relevant courses- but you can show them in a seperate table using:

No Relevant Courses = 
VAR Rel = CALCULATETABLE(VALUES(StaffTable[staff_name]); 
    FILTER(StaffTable; 
    [Countrows training courses] >= 1))
VAR staff = VALUES(StaffTable[staff_name])
Return
COUNTROWS(EXCEPT(staff; Rel))

And again, using this measure in the filter pane:

[kentyler]

This is a calculated staff table, you will probably want to create a real one and load it
The staff table has a relationship to the training table

and i added a calculated column to the training table to detect relavant trainings, you probably would want to have a "training module" table, with a true/false flag to mark the relevant trainings as detecting them by parsing strings is kind of error prone.

since you said you wanted to end up with a table, rather than a report

I added the columns you asked for to the staff table.
this is the code for the date column: 

Date = calculate(max(training[training_date]),training[Relevant])

this code uses the relationship between the staff table and the training table. That's why it doesn't have to specify that it wants the max for a particular person, the relationship is already filtering the training table for the trainings for the person in the row the code is executing in.
Since the "relevant" field has true false values, it can be used as a filter in the calculate statement, to just consider the trainings where it is true.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.