Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
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
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!
deviceid | Event Type | Event Time | Event Date |
A Door 30 | Forced | 7:35:56 AM | 1/1/2017 |
A Door 30 | Forced | 7:36:00 AM | 1/1/2017 |
B Door 10 | Invalid | 7:36:10 AM | 1/1/2017 |
D Door 61 | Push | 7:37:49 AM | 1/1/2017 |
A Door 61 | Unlocked | 7:38:07 AM | 1/1/2017 |
A Door 61 | Forced | 7:38:16 AM | 1/1/2017 |
T Door 1 | Invalid | 7:40:16 AM | 1/1/2017 |
O Door | Unlocked | 7:46:04 AM | 1/1/2017 |
A Door 12 | Invalid | 7:46:27 AM | 1/1/2017 |
T Door 1 | Invalid | 7:46:42 AM | 1/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.
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
Hi @stillmakinnoise,
With respect to the dataset that you have posted, what exact result are you expecting?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |