cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stillmakinnoise Frequent Visitor
Frequent Visitor

Total count of events occuring within specified duration

I'm brand new to this, so hopefully my explanation is clear enough. Please let me know if additional explanation is required! 

 

I have four columns within my data: Device ID, Event Type, Event Date, and Event Time. I'm looking for a count of when three specific Event Types (i.e. Event Type 1, 2, and 3) occured on the same Device ID within a specify duration of time (e.g. 30 seconds).  

 

For example, on 1/1/2017, Event Type 1 occured on Device ID 1 at 5:15:25, and Event Type 2 occured on Device ID 1 at 5:15:34 and Event Type 3 occured at 5:15:45. I'd like to know the total times this happened on any device. The combined occurence could be defined as a new Event Type. 

 

Thanks in advance to anyone who can assist! 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Total count of events occuring within specified duration

Hi @stillmakinnoise,

 

I modify the formula to let it works on sample data, please try it if it works on your side.

 

Measure:

Total Count of Speicfic Range = 
VAR current_device =
    LASTNONBLANK ( 'Table'[deviceid], [deviceid] )
VAR current_date =
    min(  'Table'[Event Date] )
VAR current_time =
    min ( 'Table'[Event Time] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            [deviceid] = current_device
                && [Event Date] = current_date
                && [Event Time] >= current_time
                && [Event Time] <= current_time + 60
        )
    )

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Total count of events occuring within specified duration

Hi @stillmakinnoise,

 

Based on your description, you want to get the total count to specific time range and group by device Id, right?

If this is a case, you can try to use below formula:

Measure:

Total Count of Speicfic Range =
VAR current_device =
    LASTNONBLANK ( table[deivce id], [device id] )
VAR current_date =
    MAX ( table[Event Date] )
RETURN
    CALCULATE (
        COUNTROWS ( table ),
        FILTER (
            ALL ( table ),
            [device id] = current_device
                && [Event Date] = current_date
                && [Event Time] >= min_Range
                && [Event Time] <= max_Range
        )
    )

 

If above not help. please share some sample data and detail contents to help us clarify your requirement.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
stillmakinnoise Frequent Visitor
Frequent Visitor

Re: Total count of events occuring within specified duration

Hi @v-shex-msft and thanks for your reply! Below is a sample of the data, which I probably should've included in the first place! 

 

 

deviceidEvent TypeEvent TimeEvent Date
A Door 30Forced7:35:56 AM1/1/2017
A Door 30Forced7:36:00 AM1/1/2017
B Door 10Invalid 7:36:10 AM1/1/2017
D Door 61Push7:37:49 AM1/1/2017
A Door 61Unlocked7:38:07 AM1/1/2017
A Door 61Forced7:38:16 AM1/1/2017
T Door 1Invalid7:40:16 AM1/1/2017
O DoorUnlocked 7:46:04 AM1/1/2017
A Door 12Invalid 7:46:27 AM1/1/2017
T Door 1Invalid7:46:42 AM1/1/2017

 

What I'm looking to identify is the number of times the three bolded Event Types occur on the same deviceid within 60 seconds of one another's Event Time on the same Event Date, as is demonstrated in the table.

Super User
Super User

Re: Total count of events occuring within specified duration

Hi @stillmakinnoise,

 

With respect to the dataset that you have posted, what exact result are you expecting?

Highlighted
Community Support Team
Community Support Team

Re: Total count of events occuring within specified duration

Hi @stillmakinnoise,

 

I modify the formula to let it works on sample data, please try it if it works on your side.

 

Measure:

Total Count of Speicfic Range = 
VAR current_device =
    LASTNONBLANK ( 'Table'[deviceid], [deviceid] )
VAR current_date =
    min(  'Table'[Event Date] )
VAR current_time =
    min ( 'Table'[Event Time] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            [deviceid] = current_device
                && [Event Date] = current_date
                && [Event Time] >= current_time
                && [Event Time] <= current_time + 60
        )
    )

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 922 guests
Please welcome our newest community members: