Forum Discussion
Helper IIIDAX experts, I need help with time manipulation
1. You need a complete date-time table with all the hours, no gaps. You should also have a full date table with all days in the year (full years) to avoid unexpected issues.
2. No relationship between date-time table and Main table
3. Create this measure for the chart:
Measure = VAR startSlot_ = SELECTEDVALUE('date hours'[date_hour]) VAR endSlot_ = startSlot_ + (1/24) //1 hour later RETURN SUMX(Main, VAR aux_ = MIN(endSlot_,Main[end_time])- MAX(startSlot_, Main[start_time]) VAR timeInSlot_ = IF(aux_>=0, 24*60*aux_, 0) RETURN timeInSlot_)4. See it all at play in the attached file
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Helper IIIAlB sure, here are a couple screenshots.
Here is a row from my table. As you can see, for the "duration" it shows 375.262. This is in minutes, so that comes out to a little over six hours, as shown by the start time and end time. And since the start time was at 4:06AM, the "date_hour" is at 4AM for that date.
Now when plotted on a graph, this is how it looks. This view shows all 24 hours of one date for one particular machine. That one long bar represents the 375 minute downtime event but it shows it as being within the 4AM hour, which is exactly how I told Power BI. You can also see that for the 5AM, 6AM, 7AM, 8AM, and 9AM hours it shows no downtimes, when in reality the 375 minute downtime carried over throughout those hours. Then in the 10AM hour that small sliver of a bar is for a downtime event that was a little over one minute, not related to the big one.
So I'm hoping that there is a way to kind of "spread out" the downtimes that are longer than 60 minutes, or if it goes from one hour into another hour, to show that. Because right now the visual looks misleading with how I have it.
Community Champion1. You need a complete date-time table with all the hours, no gaps. You should also have a full date table with all days in the year (full years) to avoid unexpected issues.
2. No relationship between date-time table and Main table
3. Create this measure for the chart:
Measure =
VAR startSlot_ = SELECTEDVALUE('date hours'[date_hour])
VAR endSlot_ = startSlot_ + (1/24) //1 hour later
RETURN
SUMX(Main,
VAR aux_ = MIN(endSlot_,Main[end_time])- MAX(startSlot_, Main[start_time])
VAR timeInSlot_ = IF(aux_>=0, 24*60*aux_, 0)
RETURN
timeInSlot_)
4. See it all at play in the attached file
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers