Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I need help.
I have a table with a time stamp as the example below. I have tried using this time stamp directly or creating a time table, either way I can't figure out what is wrong.
I need to calculate the total of days, hours and minutes from this by device / state. The thing is... I'm trying to calculate for how long time a device stays in a specific state (which basically means someone pressed a button, then I need the total of time out of it). The hours are not continous though, a certain device can be in a determined state from 12:00 -14:00 for example, and then start again at 16:00-17:00. Because of that, I need to calculate first the total of minutes and seconds per hour, and then the total hours per day, and total days per month.
So the outcome I'm looking for is a measure that will show a result like this:
Filter on ID 24 for month X: 5d 12h 30min
I have tried different calculates such as:
Total Time =
CALCULATE(
1.0*(MAX(production[Time]) - MIN(production[Time])),
FILTER(
production,
Hour(production[Time])
))
And:
TEST = MINX(FILTER(TimeDim, TimeDim[Hour] <=23 || TimeDim[Hour] >22 ), TimeDim[Minute] + (TimeDim[Second]/60))
But none of them yields the results I need.
Example of data:
LogData ID Date Time Hour Minute Second
1162 | 23 | 2022-06-09 | 11:21:01 | 11 | 21 | 1 |
1162 | 225 | 2022-06-09 | 11:21:01 | 11 | 21 | 1 |
1163 | 225 | 2022-06-09 | 11:21:01 | 11 | 21 | 1 |
1164 | 8 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1164 | 225 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1165 | 8 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1165 | 24 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1165 | 225 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1166 | 24 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1166 | 225 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1167 | 8 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1167 | 24 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1167 | 225 | 2022-06-09 | 11:21:04 | 11 | 21 | 4 |
1168 | 8 | 2022-06-09 | 14:21:05 | 14 | 21 | 5 |
1168 | 225 | 2022-06-09 | 14:21:05 | 14 | 21 | 5 |
1169 | 225 | 2022-06-09 | 14:21:05 | 14 | 21 | 5 |
1170 | 24 | 2022-06-09 | 14:21:05 | 14 | 21 | 5 |
1170 | 31 | 2022-06-09 | 14:21:05 | 14 | 21 | 5 |
I appreciate any help
Solved! Go to Solution.
I finally found a solution, following this post:
https://community.powerbi.com/t5/Desktop/Time-in-State/m-p/941429
In case anyone has the same problem.
Hi @JulianaMacedo,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
I finally found a solution, following this post:
https://community.powerbi.com/t5/Desktop/Time-in-State/m-p/941429
In case anyone has the same problem.
Just wondering... Why don't you calculate this in Power Query or in the source system? Would that not be so much easier? You could calculate this in Power Query with ease since PQ has all the needed functions to extract the pieces of info you need from a period of time. It's doable in DAX as well but with much, much more effort.
You could pre-calculate the number of minutes (as this is you base granularity) between each two consecutive dates for any one ID and place them in a table from which you'd only need to read them out and sum up if desired. Then, once you've got the total number of minutes, it'd be easy to retrieve the number of days, hours and remaining minutes... right?
Hi, it is actually a good idea... it was what I was thinking during the weekend that it would be better maybe to aggregate it before and make an aggregated table out of it before moving to DAX.
But I'm still not sure if PQ has what I need or it will be just as much work as in DAX, maybe just using SUMMARIZE function in DAX might do it.
I'll have to test a bit to find a solution, thanks for the idea.
Hi again
If you need to pre-calculate something, Power Query is the way to go. ALWAYS. This component of PBI is more than capable of doing any calculation (even recursive, where DAX is useless)... and with ease that you very often won't find in DAX.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |