Duration of a data row

[fishboneox]

Since a few days I'm struggeling with PowerBI.

Using a movementsensor on an Arduino Board I'm recording the movement and standing of an object. Beside the movement (or standing) value I'm recording the timestamp as well. I'm getting a row including timestamp and movementtyp (standing=1 movement=0) every minute. Also I have an index row (not in the picture)

Now I want to calculate the duration of a standing periode.

I've tried to create an idividual ID for every movement or calculate the difference between the first and last value after/befor a blank but failed.

Any idear from your side?

[v-piga-msft · Accepted solution]

Hi @fishboneox,

Assuming that you have create the index column begin with 1.

Then you could create the calculated column below to get the group of Standing.

Column = 
VAR currentIndex = [Index]
VAR big0 =
    CALCULATE (
        MAX ( Table1[Index] ),
        FILTER ( 'Table1', Table1[Index] <= currentIndex && 'Table1'[Standing] = 0 )
    )
VAR big1 =
    CALCULATE (
        MAX ( Table1[Index] ),
        FILTER ( Table1, Table1[Index] <= currentIndex && Table1[Standing] = 1 )
    )
RETURN
    IF (
        [Index] > big0
            && [Index] <= big1,
        CALCULATE (
            MIN ( Table1[Index] ),
            FILTER ( Table1, 'Table1'[Index] > big0 && Table1[Index] <= currentIndex )
        ),
        BLANK ()
    )

Then you could use the maximum time minus the minimum time with the formula below.

Column 2 =
VAR mi = [Column]
RETURN
    IF (
        'Table1'[Column] = 0,
        BLANK (),
        CALCULATE (
            MIN ( 'Table1'[TimeStamp] ),
            FILTER ( 'Table1', 'Table1'[Column] = mi )
        )
            - CALCULATE (
                MAX ( 'Table1'[TimeStamp] ),
                FILTER ( 'Table1', 'Table1'[Column] = mi )
            )
    )

Then you could get the standing periode.

Hope this can help you!

Best Regards,

Cherry

[fishboneox]

Hi @v-piga-msft,

thanks for this solution. It's exactly what I was looking for.It's working perfectly for 8000 rows.

Unfortunately I'm not able to try it with in my full data set because of an RAM overflow. It works with up to 10.000 rows but I#m trying to handle 800.000. I'm working with 8GB RAM but I doubt that even with a "little" more RAM it'll work. Do you have any idea how to solve this?

[v-piga-msft]

Hi  @fishboneox,

If you could add more space for the RAM that should be better.

Best Regards,

Cherry

[alexei7]

Hi fishboneox,

I think it'd be helpful to know what you are trying to do with this.

Are you trying to just add in the extra column in your screenshot where duration is

a) the duration of the whole period against each row?

b) accumulating duration going up by 1 each row?

Or are you trying to create a summary table, which would just show the start time, end time and duration?

Where are you trying to get to?