Forum Discussion
Create multiple measures to count alarms in various time buckets
SO i have a table with Alarm ID, Alarm_TImeSet, Alarm_TImeReset, AlarmName, StationName, SystemName.
Some alarms go into multiple time buckets, they start at 1200 am but continue till 1230 am, so they should be counted in 3 buckets, and so on.
Time bucket table
00:00:00
00:15:00
00:30:00
00:45:00
So, i want a way to understand how i can create another table with geenral dateTime values and Time bucket values. If there are no alarms in that bucket, the measure should return 0. SO i want to create multiple measures for all counts from zero alarms to 7 alarms and more than 7. Please advise.
Match = VAR b = // starting second for the selected 15 minute bucket of the selected day ROUNDDOWN ( ( MAX ( 'Time Buckets'[Column1] ) + MAX ( 'Calendar'[Date] ) ) * 86400, 0 ) VAR b2 = // all seconds for the selected 15 minute bucket of the selected day GENERATESERIES ( b, b + 899 ) VAR a = ADDCOLUMNS ( 'Table', "m", COUNTROWS ( INTERSECT ( b2, // all seconds in the bucket GENERATESERIES ( // all seconds in the alarm ROUNDDOWN ( [Set] * 86400, 0 ), ROUNDDOWN ( [Reset] * 86400 - 1, 0 ) ) ) ) ) RETURN SUMX ( a, [m] ) // how many seconds in the bucket match the seconds in the alarmThere's not really much to it. Datetime values can be divided into the Integer part (day) and the fraction part (time). For easier calculation and to avoid rounding errors all values are multiplied by 86400 (the number of seconds in a day) which then results in simpler integer math.
INTERSECT does most of the work by matching seconds between the two intervals.
9 Replies
- lbendlin
Super User
How granular are you alarm start and end times? Minute level or lower?
Usual approach is to use COUNTROWS(INTERSECT())
- Roy1790New Member
Mon, 03 Mar 2025 00:04:53 TimeSet
Mon, 03 Mar 2025 00:05:17 TimeReset- lbendlin
Super User
This is the time in seconds the selected timers spend in each bucket. Reset Timestamp is excluded.
Match = var b=ROUNDDOWN((MAX('Time Buckets'[Column1])+max('Calendar'[Date]))*86400,0) var b2 = GENERATESERIES(b,b+899) var a = ADDCOLUMNS('Table',"m", countrows(INTERSECT(b2,GENERATESERIES(ROUNDDOWN([Set]*86400,0),ROUNDDOWN([Reset]*86400-1,0))))) return sumx(a,[m])
- v-echaithra
Community Support
Hi Roy1790 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E. - v-echaithra
Community Support
Hi Roy1790 ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E. - v-echaithra
Community Support
Hi @Roy1790 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E.