Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need some help regarding an issue that i am facing.
I have a column Tag that represents a machine. And they have a RA_Time(start time) and RT_Time(Runs till/end time) with a schedule of Daily(runs Daily based on weekday/weekend),Hourly(runs Hourly in a day based on weekday/weekend) and Weekly (runs only once based on On Day) .
We are trying to see Tag wise in a day the time slot a machine was free in 24 hours.
Let say Machine 7 runs daily on weekdays from 4:15 pm to 4:45 pm(Daily), 5:00 p.m to 5:30 p.m(Weekly -only on Sunday),6:00 pm to 6:00 am(Daily - till next day) .
The free time slots today(Thursday) in a 24 hrs format would be :
Free slot on Thursday - 12:00 AM - 4:15 PM , 4:45 PM - 6:00PM
Free slot on Sunday - 12:00 AM - 4:15 PM,4:45 PM - 5:00 PM, 5:30 PM - 6:00 PM
I am attaching the screenshot containing the data rows.
Any help on the above would be very appreciated.
@amitchandak @Greg_Deckler @parry2k @MFelix
Thanks,
Deepak
Hi @DeepakJha23 ,
If i understand you correctly,
1# Use DATEDIFF() function to calculate the time ranges per row.
2# Sum up the time ranges with same tags per day. You might use ALLEXCEPT() function.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Maybe a variation of https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364
Not exactly sure how you are wanting to display this.
I would create a time/ date table with [Time& Date} collumn
Then use If statement to create a new custom column [ STatus] = IF [Time& Date] >= [Start Date] && [Time&Date] <= [Finish Date] then "Busy" else "Free"
Then you could filter by first and last to give you the ranges . With this solution you would need to split the tables (per tag).
There probably is a cleaner and more elegant way of doing this.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |