cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

DAX Calculating Run Time from Timestamps

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])
)
)

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.

1 ACCEPTED SOLUTION

https://youtu.be/xN2IRXQ2CvI

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
3 REPLIES 3

https://youtu.be/xN2IRXQ2CvI

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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.

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.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors