Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
I am currently trying to calculate the yield of an industrial machine.
The machine append a line into a database everytime it does something (start a production cycle, maintenance cycle, idle, etc.)
The fact table is shaped like this:
MachineID | StateID | StateValue | StateBegin | StateEnd | StateDuration |
M-001 | 1 | Production | 29/06/2022 10:30:53 | 01/07/2022 14:33:32 | |
M-001 | 2 | Idle | 01/07/2022 14:33:32 | 01/07/2022 14:45:06 | |
M-001 | 3 | Maintenance | 01/07/2022 14:45:06 | 01/07/2022 20:10:03 | |
M-001 | 4 | Idle | 01/07/2022 20:10:03 | 04/07/2022 06:03:14 |
I use a date table and want with a slicer of it retrieve only the values that are in the range.
Here is a little scheme of it and the 4 cases i have identified.
Begin --> StateBegin Column in the table
End --> StateEnd Column in the table
Min --> Slicer's lower boundary
Max --> Slicer's higher boundary
I created 2 measures that returns the min and max of the slicer:
Max= CALCULATE(MAX(Dim_temps[Date]),ALLSELECTED(Dim_temps))
Min= CALCULATE(MIN(Dim_temps[Date]),ALLSELECTED(Dim_temps))
And I started working on 2 measures that will adapt the state's begin and end datetimes depending on slicer's value
The "begin date" kinda works:
My ultimate goal would be to recover the time in the slicer's range and exclude the grayed areas in the picture.
Maybe it is possible to do it by splitting every multiple day states in multiple single day line.
But I have not seen any Power Query, DAX or SQL code allowing you to do this efficiently AND keeping last day's time.
Solved! Go to Solution.
Hi @PaulFICOT ,
Please add these custom columns.
DateListNegin = if DateTime.Date( [StateBegin] ) = [DateList] then [StateBegin] else DateTime.From( [DateList] & #time(0,0,0))
DateListEnd = if DateTime.Date( [StateEnd] ) = [DateList] then [StateEnd] else DateTime.From( [DateList] & #time(23,59,59))
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @PaulFICOT ,
Please try the custom column in Power Query.
= let BeginDate = Date.From([StateBegin]), EndDate = Date.From([StateEnd])
in List.Dates(BeginDate, Duration.Days(EndDate - BeginDate) + 1,#duration(1, 0, 0, 0))
Then expand the list to new rows.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-kkf-msft,
It is almost what I need.
The only issue is that the time information disapear.
I want to find a way to split my multiple day datetime ranges in single day with the date.
I see two ways of doing it :
Example with the StateID 1 from your PBIX:
Your solution:
Result needed:
SOLUTION1
MachineID | StateID | StateValue | StateBegin | StateEnd | StateDuration | DateList |
M-001 | 1 | Production | 29/06/2022 10:30:53 | 01/07/2022 14:33:32 | 809 | 29/06/2022 |
M-001 | Production | 29/06/2022 10:30:53 | 01/07/2022 14:33:32 | 1439 | 30/06/2022 | |
M-001 | 1 | Production | 29/06/2022 10:30:53 | 01/07/2022 14:33:32 | 873 | 01/07/2022 |
SOLUTION2
MachineID | StateID | StateValue | StateBegin | StateEnd | StateDuration | DateListNegin | DateListEnd |
M-001 | 1 | Production | 29/06/2022 10:30:53 | 01/07/2022 14:33:32 | 3122 | 29/06/2022 10:33:53 | 29/06/2022 23:59:59 |
M-001 | Production | 29/06/2022 10:30:53 | 01/07/2022 14:33:32 | 3122 | 30/06/2022 00:00:00 | 30/06/2022 23:59:59 | |
M-001 | 1 | Production | 29/06/2022 10:30:53 | 01/07/2022 14:33:32 | 3122 | 01/07/2022 00:00:00 | 01/07/2022 14:33:32 |
If you have any other idea 🙂
Regards,
Hi @PaulFICOT ,
Please add these custom columns.
DateListNegin = if DateTime.Date( [StateBegin] ) = [DateList] then [StateBegin] else DateTime.From( [DateList] & #time(0,0,0))
DateListEnd = if DateTime.Date( [StateEnd] ) = [DateList] then [StateEnd] else DateTime.From( [DateList] & #time(23,59,59))
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |