Loop through sequence

[Sza_Zsa]

Hi,

I’m looking to get total times 1 (when is 15 min in a row or longer ) -  Below a picture of what im working with now: 

RuimtenummerTechnisch	TijdstempelCEST	SensorWaarde	Previous Date 2.0	Timestamps With Occupancy 2.0
0.25	9-8-2022 11:15	0	9-8-2022 11:15	0
0.26	9-8-2022 11:15	1	9-8-2022 11:15	0
0.25	9-8-2022 11:20	0	9-8-2022 11:20	0
0.26	9-8-2022 11:20	1	9-8-2022 11:15	5
0.25	9-8-2022 11:25	0	9-8-2022 11:25	0
0.26	9-8-2022 11:25	1	9-8-2022 11:15	10
0.25	9-8-2022 11:30	0	9-8-2022 11:30	0
0.26	9-8-2022 11:30	1	9-8-2022 11:15	15
0.25	9-8-2022 11:35	0	9-8-2022 11:35	0
0.26	9-8-2022 11:35	1	9-8-2022 11:15	20
0.25	9-8-2022 11:40	0	9-8-2022 11:40	0
0.26	9-8-2022 11:40	1	9-8-2022 11:15	25

 I have create a measure with the help of SQLBI to find first time with when sensorwaarde is one in a sequence. The measure I created for this is: 

Previous Date 2.0 =

    VAR CurrentDate = MAX(Metingen[TijdstempelCEST])

    VAR FirstDateEver =

        CALCULATE( MIN(Metingen[TijdstempelCEST]), REMOVEFILTERS())

    VAR AllTimestampsWithoutOccupancy =

    CALCULATETABLE(

        values(Metingen[TijdstempelCEST]),

        Metingen[SensorType] = "Occupancy",

        Metingen[SensorWaarde] < 1,

        REMOVEFILTERS(Metingen[TijdstempelCEST])

    )

    Var PrevTimestampWithoutOccupancy =

        MAXX(

            FILTER(AllTimestampsWithoutOccupancy, Metingen[TijdstempelCEST] <= CurrentDate),

            Metingen[TijdstempelCEST]

        )

    VAR PrevDate =

        COALESCE(PrevTimestampWithoutOccupancy, FirstDateEver)

    VAR Result =

        DATEDIFF(CurrentDate, PrevDate, MINUTE) * -1

    RETURN

        PrevDate

The problem that I’m facing is that I can only take the [sensorwaarde] = 1 when this was 3 times in a row (so 15 minutes) and when it  was in the same room. To continue I wanted to create a nested loop and iterate through each sequence. When the sum of 1’s of each sequence would be >= 3 then I would take sum and add it to total times 1 else I would discard the data. The problem is there not a real way to loop to through the data. So I was hoping someone has a trick to do this? I’m quiet new to DAX so hope someone can help out.

Thanks in advance!

[v-xiaosun-msft]

Hi @Sza_Zsa ,

According to your description, some column fields of the measure such as [SensorType]  do not appear in the table you provided, can you provide the complete sample data? In my understahding, I think it is 5 minutes to go through a cycle, but what is the 15 minutes you mentioned? Please provide the loop nesting you want in more details to help you better.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.