Forum Discussion
Determining Actual Machine Operation Time by Filtering Out Test Period in Power BI
Apologies for not expressing myself clearly. Some parts were not precise, and there were inaccuracies in the data I provided.
Here's a link to a more accurate version.
https://drive.google.com/file/d/1ruNicSeQlvUZifFZ4_C6Y1rSQtqYsNwE/view?usp=sharing
Firstly, WorkHourTemp is used to observe the machine's operation process.
The process of determining WorkHourTemp:
'Event clock-in' signifies someone sensing and starting the machine.
'Event clock-out' indicates someone sensing and stopping the machine.
In this process:
If 'clock-in' occurs before 'clock-out,' it means someone is operating the machine. If the sensor detects an mv change greater than 1, it calculates WorkHour, representing personnel operating the machine and performing specific actions.
If 'clock-out' occurs before 'clock-in,' it implies no machine operation by anyone. Even if the sensor detects an mv change, WorkHour will not be calculated.
Using Machine 2 as an example:
There are two scenarios:
First Scenario:
The blue-boxed timeframe for Machine 2 starts at 12:02 AM on July 23, 2023, and ends at 12:12 AM on the same day. This period only accounts for 0.17 hours, theoretically considered a testing period as we usually conclude machine tests within 1 hour.
Second Scenario:
The red-boxed timeframe for Machine 2 starts at 10:24 AM and ends at 12:10 PM on July 23, 2023. This period includes actual operational time of 0.68 hours + 1.08 hours, clearly not a testing period.
The calculation of 0.68 and 1.08 arises from mv changes detected by the sensor. Hence, WorkHourTemp records these two data entries.
Currently, I aim to exclude the testing period and calculate only the actual time personnel operated the machine, thereby eliminating the first scenario. In other words, I would prefer the value of 0.17 hours not to be included, retaining only the values of 0.68 hours + 1.08 hours to determine the actual operational time, excluding the testing period.
Revisiting Machine 1:
There are four red-boxed instances indicating that between 'clock-in' and 'clock-out,' personnel utilized the machine for four specific actions.
The sum of times within these red boxes is evidently more than 1 hour, signifying non-testing periods.
However, the time sum within the blue-boxed area of Machine 1 doesn’t exceed 1 hour. Therefore, I would appreciate a method to exclude the values within the blue boxes, keeping only the total time of personnel operating the machine for specific actions.
Additional Note: During the testing period, the sensor records a maximum of 15 data entries within 1 hour.
Hi Bubufun
Here is my solution for you, you can create a new column implementation in Power BI Desktop:
Use power query add a column as the index value
If the index value is less than or equal to the current index value and temp is 0, the maximum index value is returned
max_index = CALCULATE ( max ('Table'[Index] ), FILTER('Table', 'Table'[Index] <=EARLIER('Table'[Index]) && 'Table'[temp] = 0) )When temp is 0, it returns 0, and group_index when it is not equal to 0
group_index = IF([temp] = 0, 0, [max_index])Temp sum for the same column value
temp_value = SUMX(FILTER('Table', 'Table'[group_index] = EARLIER('Table'[group_index])), [temp])Determine whether the sum of values between 0 is greater than 1 and return the required value
result = IF([temp_value] = 0, 0, if([temp_value] < 1, 0, if([temp_value] > 1, [temp])))Finally, you can make a report to show the result
Best Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Bubufun , Assuming WorkHourTemp is a measure create a measure like
Averagex(Summarize(Table, Table[Name], Table[UTC], Table[Event], "_1", Switch(True(), [WorkHourTemp]<1, 0,1 )), [_1])
HI! amitchandak
Apologies for not expressing myself clearly. Some parts were not precise, and there were inaccuracies in the data I provided.
Here's a link to a more accurate version.
https://drive.google.com/file/d/1ruNicSeQlvUZifFZ4_C6Y1rSQtqYsNwE/view?usp=sharingFirstly, WorkHourTemp is used to observe the machine's operation process.
The process of determining WorkHourTemp:
'Event clock-in' signifies someone sensing and starting the machine.
'Event clock-out' indicates someone sensing and stopping the machine.
In this process:If 'clock-in' occurs before 'clock-out,' it means someone is operating the machine. If the sensor detects an mv change greater than 1, it calculates WorkHour, representing personnel operating the machine and performing specific actions.
If 'clock-out' occurs before 'clock-in,' it implies no machine operation by anyone. Even if the sensor detects an mv change, WorkHour will not be calculated.
Using Machine 2 as an example:There are two scenarios:
First Scenario:
The blue-boxed timeframe for Machine 2 starts at 12:02 AM on July 23, 2023, and ends at 12:12 AM on the same day. This period only accounts for 0.17 hours, theoretically considered a testing period as we usually conclude machine tests within 1 hour.
Second Scenario:
The red-boxed timeframe for Machine 2 starts at 10:24 AM and ends at 12:10 PM on July 23, 2023. This period includes actual operational time of 0.68 hours + 1.08 hours, clearly not a testing period.The calculation of 0.68 and 1.08 arises from mv changes detected by the sensor. Hence, WorkHourTemp records these two data entries.
Currently, I aim to exclude the testing period and calculate only the actual time personnel operated the machine, thereby eliminating the first scenario. In other words, I would prefer the value of 0.17 hours not to be included, retaining only the values of 0.68 hours + 1.08 hours to determine the actual operational time, excluding the testing period.
Revisiting Machine 1:
There are four red-boxed instances indicating that between 'clock-in' and 'clock-out,' personnel utilized the machine for four specific actions.
The sum of times within these red boxes is evidently more than 1 hour, signifying non-testing periods.
However, the time sum within the blue-boxed area of Machine 1 doesn’t exceed 1 hour. Therefore, I would appreciate a method to exclude the values within the blue boxes, keeping only the total time of personnel operating the machine for specific actions.
Additional Note: During the testing period, the sensor records a maximum of 15 data entries within 1 hour.
Hi Bubufun
Here is my solution for you, you can create a new column implementation in Power BI Desktop:
Use power query add a column as the index value
If the index value is less than or equal to the current index value and temp is 0, the maximum index value is returned
max_index = CALCULATE ( max ('Table'[Index] ), FILTER('Table', 'Table'[Index] <=EARLIER('Table'[Index]) && 'Table'[temp] = 0) )When temp is 0, it returns 0, and group_index when it is not equal to 0
group_index = IF([temp] = 0, 0, [max_index])Temp sum for the same column value
temp_value = SUMX(FILTER('Table', 'Table'[group_index] = EARLIER('Table'[group_index])), [temp])Determine whether the sum of values between 0 is greater than 1 and return the required value
result = IF([temp_value] = 0, 0, if([temp_value] < 1, 0, if([temp_value] > 1, [temp])))Finally, you can make a report to show the result
Best Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi! Anonymous
Thank you for your helpful response! It really solved the issue I was facing.
It was exactly what I needed to solve my problem!!
