Forum Discussion

jeroenwmwillems's avatar
jeroenwmwillems
Icon for Advocate I rankAdvocate I
6 years ago
Solved

Flag 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

Need Help
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    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's avatar
    JarroVGIT
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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's avatar
      jeroenwmwillems
      Icon for Advocate I rankAdvocate I
      6 years ago

      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!