Filter and Display Date based on Latest Duplicate Record

[HPax]

Hi all, 

This seems like it should be an easy thing to do, but I'm struggling to write a formula to display the latest date in my matrix for a specific record. My data contains duplicate records which are distiguishable by different dates. When a duplicate is found, I want the most recent date to display. I can easily set the date column to the latest date, but then it only pulls the most recent date from the entire data source. Even if my data source didnt include duplicates, I'm still struggling to write a formula that pulls the dates for that specific row. 

I created some sample data to show you what I'm looking to achieve. 

 Thanks in advance! 

[gmsamborn]

Hi @HPax 

I created a measure to filter your visual.

_Include = 
VAR _CurDt = MAX( 'Table'[Date] )
VAR _MaxDt =
    CALCULATE(
        MAX( 'Table'[Date] ),
        REMOVEFILTERS( 'Table'[Date] )
    )
VAR _Result =
    IF(
        _CurDt = _MaxDt,
        1,
        0
    )
RETURN
    _Result

 Let me know if you have any questions.

Filter and Display Date based on Latest Duplicate Record.pbix

[HPax]

Hi gmsamborn, 

Thanks for the quick response! This did partially work, however its still pulling the most recent date from a record that shares the same topic. Using my example above, the matrix shows January 20th for Topic 2, regardless of the name associated with the record. If I'm filtering based on Joe, then that date is correct. But if if I'm filtering based on Trish, it should show January 14th. 

[gmsamborn]

Hi @HPax 

In your original request, you didn't mention a filter (but that shouldn't change my results).

You mention "My data contains duplicate records which are distiguishable by different dates." I took this as meaning you want the latest date for each combination of Topic and Name. Can you verify that is what you want?

On page 2 of the attached pbix, I set up a slicer based on Name. When I filter on Trish, I see 4 records: Topic 1, Topic 2, Topic 3, and Topic 4. In the source data, only Topic 4 has 2 records for Trish, of which I'm only including the record with a date of 21-Jan-25 (Latest Date of the 2 records).

Can you verify this?

Filter and Display Date based on Latest Duplicate Record - 2.pbix

[HPax]

Hi @gmsamborn,

Thanks for sharing the file! But I'm still having issues. I think is because the example I gave didnt accurately reflect my data. I've provided more detail below so you can understand my scenario better.  

(3) Online lists  

Employee List 

Data contains - Employee name and their training level (lookup column to training matrix) 

Relationship - Training Level column is linked to Training Matrix - Training Level column  

Training Matrix 

Data contains-  All training topics and the training level they are included in 

Relationship - Training Topic is linked to Training Tracking - Training Topic   

Training Tracking 

Data Contains - Data from PowerApps (filter into list) when training is completed. This includes the employee name (lookup column to Employee List) and Training Topic (lookup column to Training Matrix)

The purpose of this report is to track training for each employee.  

The required training varys depending on the employees role. So employees will have seperate and muliple training requirements. For example, Trish's role requires she complete Level A training (which included 6 topics). But Joe has multiple roles which differ from Trish's, so he would have training requirements in more than one Level (eg. Level B & Level G).  

So, my report will show all the required training for that employee, the completed training, and the most recent date the training was completed (if duplicate records found). If no training records are found, leave it blank. As an example, Trish needs to complete Topics 1-6, but only has records for Topics 1-4. So for topics 5 & 6, I'd still like them shown on the matrix.

Sorry for not providing all this info originally. I thought I could get away with a basic example instead of describing my entire sceanrio. I also can't share my .pbix file with you as our organizations permission levels won't allow it. If required, I can try to put together some dummy data for you the review. But I did screen shot my Matrix so you can see my visual. 

Screenshot without your formula

Note the date circled red. Theres no training found for that topic under that employee, but a date is shown because that topic is still present in the data source. Just recorded under another employees name. 

Screenshot with your fomula

Doesnt filter based on required training and dates still seem to be pulling the most recent date from the entire list. 

Thanks for all your help and patience so far!!

[gmsamborn]

Hi @HPax 

Thanks for the additional detail.  That is quite a bit to digest.

Since the data has changed (a lot), is there any way you can create a pbix reflecting the changes?  (An xlsx with a sheet for each table would work.)

It doesn't need to have a lot of data - just a few records.  Just make sure it covers your issue.

Thanks

[HPax]

Hi @gmsamborn,

I have put together some sample data for you in the attached .pbix file. I will be away for the next 10 days, so I wont respond until the first week of March. But I really appreciate how much you're helping me! Hopefully I've given you enough to work with. 

One thing Ill mention, as I don't know if its important or not; in my original data, the "Trainees" in the Training Tracking List, and the "Training Level" in the Employee list is lookup column that allows multiple selections. So when I transformed my data, I expanded these list columns to include one Trainee and/or one training level per row.  However, I used Excel to create the data in the link below which isnt linked in the same manner. So I manipulated the data so that its laid out in the same way. Hope that makes sense! 

Thanks again!!  

Training Example.pbix 

[Anonymous]

Hi @HPax ,

It appears that the shared PBIX file URL contains an error. Could you please share it again with the appropriate permissions? Thank you.

Best Regards

[HPax]

Hi @gmsamborn,

Sorry about that. The below link should work now. 

Training Example.pbix