Forum Discussion

Sza_Zsa's avatar
Sza_Zsa
Regular Visitor
3 years ago

Loop through sequence

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: 

RuimtenummerTechnischTijdstempelCESTSensorWaardePrevious Date 2.0Timestamps With Occupancy 2.0
0.259-8-2022 11:1509-8-2022 11:150
0.269-8-2022 11:1519-8-2022 11:150
0.259-8-2022 11:2009-8-2022 11:200
0.269-8-2022 11:2019-8-2022 11:155
0.259-8-2022 11:2509-8-2022 11:250
0.269-8-2022 11:2519-8-2022 11:1510
0.259-8-2022 11:3009-8-2022 11:300
0.269-8-2022 11:3019-8-2022 11:1515
0.259-8-2022 11:3509-8-2022 11:350
0.269-8-2022 11:3519-8-2022 11:1520
0.259-8-2022 11:4009-8-2022 11:400
0.269-8-2022 11:4019-8-2022 11:1525

 

 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!

1 Reply

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Community Support
    3 years ago

    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.