Forum Discussion
Create multiple measures to count alarms in various time buckets
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.
Super UserHow granular are you alarm start and end times? Minute level or lower?
Usual approach is to use COUNTROWS(INTERSECT())
Mon, 03 Mar 2025 00:04:53 TimeSet
Mon, 03 Mar 2025 00:05:17 TimeReset
- lbendlin
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])Can u please explain in a little detail and all the steps. I am still a novice to Power BI and learning the ropes.
- lbendlin
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.
ryan_mayucould you pls provide more sample data?