Forum Discussion
Advocate IFlag double events in a data set, based on timing
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
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! 🙂
2 Replies
- JarroVGIT
Resident Rockstar
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! 🙂
jeroenwmwillemsA 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!