Forum Discussion
Count same value in interval
Hello,
I have a problem to identify how many days a machine operates in a role every time it starts operating. If it was already operating on day one it should not be counted. The output for days in a role would be as follows.
Apreciate any help in this matter.
| Day | Machine | Status | Status Change | Days in a role |
| day 1 | Machine 1 | Operating | 0 | |
| day 1 | Machine 2 | Operating | 0 | |
| day 1 | Machine 3 | Operating | 0 | |
day 2 | Machine 1 | Stopped | 0 | |
| day 2 | Machine 2 | Operating | Started operating | 1 |
| day 2 | Machine 3 | Operating | 1 | |
| day 3 | Machine 1 | Stopped | 0 | |
| day 3 | Machine 2 | Operating | Started operating | 1 |
| day 3 | Machine 3 | Stopped | Stopped | 0 |
Thank you for following the question. The ideia is to count the number of days the machines are operating after starting. Machine 2 started operating on day 2 and is still on, hence it counts day 2 and 3.
I was able to come up with this solution.Days_in_a_role =
VAR dateOperationChange = CALCULATE(MINX('Table','Table'[day]),
FILTER('Table',
'Table'[Status Change] = EARLIER('Table'[Status Change]) &&
'Table'[Status Change] = "Started Operating"
)) return
VAR countChangeState = CALCULATE(
COUNTX('Table','Table'[Status Change]),
FILTER('Table',
'Table'[Machine] = EARLIER('Table'[Machine]) &&
'Table'[Status Change] = "Started Operating")
) return
VAR firstRowIsOperation = CALCULATE(MINX('Table', 'Table'[Status]), FILTER('Table', 'Table'[Machine] = EARLIER('Table'[Machine]))) = "Started Operating" return
VAR checkIsOpational =
IF( firstRowIsOperation && countChangeState = 0, false,
IF(firstRowIsOperation && countChangeState > 0, true,
IF(firstRowIsOperation = false && countChangeState > 0, true, false)
)
) return
VAR checkIsOperation = IF(CALCULATE(
MAXX('Table','Table'[Status]),
FILTER('Table',
'Table'[Machine] = EARLIER('Table'[Machine]) &&
'Table'[Status] = EARLIER('Table'[Status]) &&
checkIsOpational = true &&
'Table'[day] >= dateOperationChange
)) = "Started Operating", 1, 0) return checkIsOperation
2 Replies
Machine 2 never stopped - why is the result 1 ? Please explain the logic in more detail and maybe provide some more sample data.
Thank you for following the question. The ideia is to count the number of days the machines are operating after starting. Machine 2 started operating on day 2 and is still on, hence it counts day 2 and 3.
I was able to come up with this solution.Days_in_a_role =
VAR dateOperationChange = CALCULATE(MINX('Table','Table'[day]),
FILTER('Table',
'Table'[Status Change] = EARLIER('Table'[Status Change]) &&
'Table'[Status Change] = "Started Operating"
)) return
VAR countChangeState = CALCULATE(
COUNTX('Table','Table'[Status Change]),
FILTER('Table',
'Table'[Machine] = EARLIER('Table'[Machine]) &&
'Table'[Status Change] = "Started Operating")
) return
VAR firstRowIsOperation = CALCULATE(MINX('Table', 'Table'[Status]), FILTER('Table', 'Table'[Machine] = EARLIER('Table'[Machine]))) = "Started Operating" return
VAR checkIsOpational =
IF( firstRowIsOperation && countChangeState = 0, false,
IF(firstRowIsOperation && countChangeState > 0, true,
IF(firstRowIsOperation = false && countChangeState > 0, true, false)
)
) return
VAR checkIsOperation = IF(CALCULATE(
MAXX('Table','Table'[Status]),
FILTER('Table',
'Table'[Machine] = EARLIER('Table'[Machine]) &&
'Table'[Status] = EARLIER('Table'[Status]) &&
checkIsOpational = true &&
'Table'[day] >= dateOperationChange
)) = "Started Operating", 1, 0) return checkIsOperation
