Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, Good folks of Power BI. I am a new bee trying to learn and work with this fantastic tool.
I am trying to get some downtime data out from the below data set.
I am trying to get the time difference as the formal below:
Machine Stopped - Machine is running & Production Stop - Machine is Running. Basically, any event before Machine is running - Machine is running.
Any advice or direction would be greatly appreciated
Thanks a lot!
Solved! Go to Solution.
Hi @MirBatman
In case a "Machine is running" time happens on the second day after the events before it, I combine Date and Time into a DateTime column in the table for calculation. Then add a calculated column to get the duration in seconds. You can change the statement after "RETURN" to get different format per your need.
DateTime = TestTable[Date] + TestTable[Time]
Column =
VAR _endTime = MINX(FILTER(TestTable, TestTable[Column4] = "Machine is running" && TestTable[DateTime] >= EARLIER(TestTable[DateTime])), TestTable[DateTime])
VAR _duration = _endTime - TestTable[DateTime] // This duration is in Day unit.
RETURN
_duration * 24 * 60 * 60 // Convert the duration to Seconds
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @MirBatman
In case a "Machine is running" time happens on the second day after the events before it, I combine Date and Time into a DateTime column in the table for calculation. Then add a calculated column to get the duration in seconds. You can change the statement after "RETURN" to get different format per your need.
DateTime = TestTable[Date] + TestTable[Time]
Column =
VAR _endTime = MINX(FILTER(TestTable, TestTable[Column4] = "Machine is running" && TestTable[DateTime] >= EARLIER(TestTable[DateTime])), TestTable[DateTime])
VAR _duration = _endTime - TestTable[DateTime] // This duration is in Day unit.
RETURN
_duration * 24 * 60 * 60 // Convert the duration to Seconds
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thanks a lot.
This definitely helps. Just an add-on question, how can I get the below result?
CURRENT | DESIRED | ||
DateTime | Column4 | Column | Column |
10/10/2021 5:32:29 AM | Machine Stopped | 62 | 0 |
10/10/2021 5:32:29 AM | Production Stopped | 62 | 62 |
10/10/2021 5:33:31 AM | Machine is running | 0 | 0 |
10/10/2021 6:33:28 AM | Production Stopped | 348 | 51 |
10/10/2021 6:34:19 AM | Machine Stopped | 297 | 297 |
10/10/2021 6:39:16 AM | Machine is running | 0 | 0 |
10/10/2021 9:17:01 AM | Production Stopped | 38 | 38 |
10/10/2021 9:17:39 AM | Machine is running | 0 | 0 |
Along with the current query, which references " Machine is running" and subtracts all the earlier events. Can we get the duration between the consecutive events?
I am trying to get total downtime on this equipment.
Please do let me know at your convenience.
Hi @MirBatman
Can you add a sample of your expected result table/column?
Also, it'd be great if you share sample of your data as a text not an image.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
DateTime | Column4 | Column |
10/10/2021 5:32:29 AM | Machine Stopped | 0 |
10/10/2021 5:32:29 AM | Production Stopped | 62 |
10/10/2021 5:33:31 AM | Machine is running | 0 |
10/10/2021 6:33:28 AM | Production Stopped | 51 |
10/10/2021 6:34:19 AM | Machine Stopped | 297 |
10/10/2021 6:39:16 AM | Machine is running | 0 |
10/10/2021 9:17:01 AM | Production Stopped | 38 |
10/10/2021 9:17:39 AM | Machine is running | 0 |
I am trying to get the total machine downtime in seconds. I am sorry, as I do not know how to attach a sample .pbix file. I do not see an attachment option, or I do not know how to.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |