I have a dataset where we are recording pump activity in a water tank, I want to be able to show how many times in a day the pump was switched on. The sensors take readings every 5 seconds and if there is an output reading from the pump sensor then I classify it as being switched on using the following calculated column: Pump on = IF(Dataset[Pump 1 output] > 15, 1, 0)
My dataset is ordered descending and indexed by the timestamp of the sensor readings, so the newest date appears at the bottom of my table.
What I want to be able to do is assign an incremented int to each block of data where Pump on = 1.
I need it so I can work out how many times in one day the pump switched on.
Unfortunately your suggestion to use measure with DISTINCTCOUNT() didn't work, it only shows me how many distinct values there were that day. As you can see below for March 14th it only shows 2 values (ignore the first column, Drive 1 starts that is a SUM() of [Pump on]), however by manually checking the data in Excel I know that the correct value is actually 15.
Why I want the incremental count is so I can do a DISTINCTCOUNT() of the number of incremental values so I can easily identify groups of data where the pump was switched on and correctly show how many times in one day the pump came online.
If you were looking for the amount of times the pump came on in one day and you have got your [Pump On] column, what you could do is to create a Date table and create a relationship from your table using the [Timestamp] column
Then you could create a measure which will SUM('TableName'[Pump On]) and then use the Date from the Date table. This will then count the number of times the pump came on for a particular day.
As you can see with the Image below is where I have got the data from your dataset counting 13 times based on the different times it was on.
And then another screen below where I made up some data where it has occurred 5 times.
Did I answer your question? Mark my post as a solution!
Unfortunately I don't think that's going to get me the data I want.
Based on your first screenshot the number of times the pump came on should be 3 not 13. You can see that there are 3 groups of data where the [Times pump on] = 1. This is because there are 3 groups of 1 values showing that the pump was on. Once this is figured out I will also need to work out how long the pump was on for during that time, but that I think I can work out once I can identify the groups. For the sake of clarification it would be the case that the pump came on the first time at 10:38:10 and was on for 20 seconds. The second time it came on would be 10:39:00 and was on for 15 seconds. The third and final time it came on would be 10:39:35 and was on for 25 seconds.
The end totals should be something like [Pump Starts] = 3 and [Pump time on] = 50 Seconds
Hopefully that should help to show more clearly what it is that I'm trying to achive.