Flag double events in a data set, based on timing

[jeroenwmwillems]

Hi PowerBI wizards,

I was hoping you all could help me with the following problem:

I have a data set that consists of a very large amount of events of which I have a date and time stamp and a User ID code.

Sometimes it happens that a user creates multiple events. If this happens within 30 min from each other, this is a double up and I should flag this. Is there a formula I could use, which effectively checks my list for rows that have the same user ID AND where the Date & Time is only 30 min apart from each other? 

For example, the 2 rows have the same UserID and the date and time for the both rows are 12/03/2019 8:26:14 AM and 12/03/2019 8:46:14 AM. This is only 20 min apart, so the both rows are flagged by the formula.

See a screenshot of the rows and columns above.

Thanks a lot in advance for the help!

Jeroen

[JarroVGIT · Accepted solution]

Well, this one was fun! I've created a table to mimic your situation. Note that in this case and per your requirement, only row 5 should be marked.

Table3 - Note row 5 (ID=A)

I've added a calculated column with the following expression:

HasValueLessThan30MinutsAgo = 
VAR user = Table3[UserID]
VAR currentTimeStamp = Table3[TimeStamp]
RETURN
COUNTROWS(FILTER(ALL(Table3), Table3[UserID] = user && Table3[TimeStamp] > (currentTimeStamp - ((1/2) *(1/24))) && Table3[TimeStamp] < currentTimeStamp))

This counts the rows on a filtered version of the whole table, where timestamp is between currentTimestamp en currentTimestamp minus 30 minutes and where the userID is the current UserID. This results in the following:

That should work. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[jeroenwmwillems]

A true wizard indeed, thanks Djerro! 

I'm not even going to try to understand the full formula, but the results prove it works in my data set. 

Much appreciated!