Filter Duplicate Values based on earlier submission

[cbehanna]

Hi all! Love this community! I'm new here and to PowerBI!

I am trying to do something that I haven't quite seen in other support posts. I have data that is coming in via a Microsoft Form. It is connected to PowerBI Desktop via its associated Excel spreadsheet stored in Sharepoint.

What I am trying to do is "filter" out duplicate responses so that only the most recent submission of theirs visualizes. Here's an example of my data:

For example, I would only like Tom and Melissa's most recent submissions (based on completion time) to visualize. This data is live and will continue to grow/change with duplicate submissions, so I'm hoping there is a solution via Power Query or DAX to only have the most recent submission, including if they submit any future responses.

Thanks for any assistance!

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

Check = 
var a = IF(CALCULATE(COUNT('Table'[Name]),ALLEXCEPT('Table','Table'[Name]))>1,1,0)
return
IF(a=1,IF(MAX('Table'[Completion time])=CALCULATE(MAX('Table'[Completion time]),ALLEXCEPT('Table','Table'[Name])),0,1),0)

Then apply it to the original table visual and set Check=0, the result shows:

Here is my test pbix file:

pbix 

Hope this can help.

Best Regards,

Giotto Zhi

[amitchandak]

I think you can try lastnonblankvalue, part of Feb 2020 release

https://docs.microsoft.com/en-us/dax/lastnonblankvalue-function-dax