DAX Calculating Run Time from Timestamps

[Anonymous]

Hello, I need help writing a DAX that returns time intervals using timestamps. The table is below:

object_name	sensor_id	load_present	timestamp	Run Increments
Stand 8	1	1	1566532803
Stand 8	5	1	1566532805
Stand 8	8	1	1566532805
Stand 8	15	1	1566532806
Stand 8	2	1	1566532809
Stand 8	6	1	1566532810
Stand 8	9	1	1566532810
Stand 8	3	0	1566532822
Stand 8	16	0	1566532823
Stand 8	17	0	1566532823
Stand 8	4	0	1566532823
Stand 8	7	0	1566532823
Stand 8	12	0	1566532824
Stand 8	14	0	1566532824
Stand 8	1	0	1566532827
Stand 8	5	0	1566532829
Stand 8	8	0	1566532829
Stand 8	16	0	1566532829
Stand 8	17	0	1566532829
Stand 8	17	0	1566532830
Stand 8	16	0	1566532830
Stand 8	15	0	1566532830
Stand 8	17	0	1566532832
Stand 8	16	0	1566532832
Stand 8	2	0	1566532833
Stand 8	6	0	1566532834
Stand 8	9	0	1566532834
Stand 8	13	0	1566532836

There are several object_name's, each with their own set of sensors. What I want is to calculate the interval of time for which the load_present flag is on per each object_name. I don't know what the timestamps are relative to, but they are fortunately in seconds. However, I can't wrap my head around the context that filters return and all my research has only confused me further. I resorted to modifying existing formulas that users have proposed for calculating time between employees clocking in and out (https://community.powerbi.com/t5/Desktop/calculating-working-time-in-office/m-p/567548#M267842)

My version is below, and it is returning vastly wrong numbers:

(object_history_20190824_000002 is the name of the csv I'm testing this with, will eventually scale up to SQL DB)

Run Increments = 
VAR a =
    CALCULATE (
        MAX ( object_history_20190824_000002[timestamp] ),
        FILTER ( ALL ( object_history_20190824_000002 ),
            object_history_20190824_000002[ifm_object_id] = 1 && object_history_20190824_000002[ifm_name] = EARLIER(object_history_20190824_000002[ifm_name])
            && object_history_20190824_000002[timestamp] < EARLIER(object_history_20190824_000002[timestamp])
            && object_history_20190824_000002[load_present] <> EARLIER(object_history_20190824_000002[load_present])
            && object_history_20190824_000002[load_present] = 1
        )
    )

RETURN ( (object_history_20190824_000002[timestamp] - a) )

The table is default sorted by timestamp, and the next step would be to create a measure that sums the intervals in calculated column. If this can all be done in one measure, that would be amazing! Also, please feel free to educate me on my mistakes. I'd love to know why this fails.

[MattAllington · Accepted solution]

Maybe my video here will help you (power query instead of DAX). 
https://youtu.be/xN2IRXQ2CvI

[Anonymous]

I have thought about pursuing this avenue you've suggested, but I'm new to Power Bi and cautious against the performance impact of adding new columns. Most of the introductory videos/articles I've found encourage minimizing the table dimensions. Is there a large effect on Power BI's speed/performance in doing this?

Also, I have seen people get measures and calculated columns doing exactly what I want to do (https://community.powerbi.com/t5/Desktop/calculating-working-time-in-office/m-p/567548#M267842). The only difference is that my equivalent IN/OUT flag is a 1/0 and persists for thousands of rows (as opposed to just one row). If no one has a solution in that regard, I guess I'll go with self-joining.

[MattAllington]

That is a very wise consideration. Believe me, I have been doing this for six years, and training people in power BI during that entire period. You won't find a bigger critic of adding new columns than me. https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

However there are times when it is better to use a column than it is to use a measure, and my view is that this is one of those times. Power BI is excellent at doing aggregations over very large columns, but it is not good at doing comparisons between two consecutive rows in a table. I suggest you use my video to bring the timestamps you need into another column using PQ.  If possible you should then subtract the timestamps from each other to create a duration column (in seconds or minutes, or what ever you need). Then remove the second timestamp column again. I.e., it is better to have an additional duration column that is to have another timestamp column loaded.