Determining Actual Machine Operation Time by Filtering Out Test Period in Power BI

[Bubufun]

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.

[Anonymous · Accepted solution]

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.

[Bubufun]

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!!

[amitchandak]

@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])

[Bubufun]

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=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.