Filtering record by latest collection date (various values)

[samw5]

I just recently starting playing with PowerBI, working with some Azure Update Compliance.

I was able to get the data populated and filtered in PowerBI desktop for the most part but I'm hitting one roadblock.

I am trying to filter out data based on the latest scan performed. The data aggregated sometimes will show multiple similar entries. I'd like to be able to discard anything but the one from the latest scan date for each machine.  

For instance below is what I am looking at. The data shows 2 sets of records for the same machine but fetched from different dates. 
Not all machines have the same "LastScan" date so I can't use a date filter. I'm trying to figure out how to use the latest value that was collected.

[v-zhenbw-msft]

Hi @samw5 ,

We can use the Group By function in Power Query Editor and get the max date row to meet your requirement.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[lkalawski · Accepted solution]

@samw5 ,

The first thing you need to do is delete all duplicates, i.e. records that have the same values in all columns.
To do this, select all columns and click Remove Duplicates. 

Then you have to rank the records so that only the most recent are left.

Here you can find the solution: https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/

_______________
If I helped, please accept the solution and give kudos! 😀

[samw5]

That worked and was easy enough to apply. Ended up removing dups based only on computerID, ReleaseName, sort it by data (newest to oldest) then removing dups which should have gotten rid of well dups!

[pranit828]

HI @samw5 

Createa measure

_filter = 

car _max_dt = CALCULATE(MAX('table'[lastScan]),ALLEXCEPT('table'm'table'[ComputerId]))

return IF('table'[lastScan] = [_max_dt],1,0)

anas in the filter section filter the '_filter' to show only data with 1

[lkalawski]

@pranit828

This will not work where there are records with the same dates and times. Both records will have "1".

You can use the measure you presented, but after filtering "1" you have to additionally remove duplicates.

_______________
If I helped, please accept the solution and give kudos! 😀