Forum Discussion

Roy1790's avatar
Roy1790
New Member
1 year ago
Solved

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 alarm

     

    There'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

  • How granular are you alarm start and end times?  Minute level or lower?

     

    Usual approach is to use COUNTROWS(INTERSECT())

    • Roy1790's avatar
      Roy1790
      New Member

      Mon, 03 Mar 2025 00:04:53   TimeSet
      Mon, 03 Mar 2025 00:05:17    TimeReset

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper 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's avatar
    v-echaithra
    Icon for Community Support rankCommunity 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's avatar
    v-echaithra
    Icon for Community Support rankCommunity 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's avatar
    v-echaithra
    Icon for Community Support rankCommunity 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.