Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Example dataset below with example output:
Index | Timestamp | Pump 1 Output | Pump on | Example Output |
1 | 21/05/2017 22:38:00 | 0 | 0 | |
2 | 21/05/2017 22:38:05 | 0 | 0 | |
3 | 21/05/2017 22:38:10 | 50 | 1 | 1 |
4 | 21/05/2017 22:38:15 | 50 | 1 | 1 |
5 | 21/05/2017 22:38:20 | 50 | 1 | 1 |
6 | 21/05/2017 22:38:25 | 50 | 1 | 1 |
7 | 21/05/2017 22:38:30 | 50 | 1 | 1 |
8 | 21/05/2017 22:38:35 | 5 | 0 | |
9 | 21/05/2017 22:38:40 | 0 | 0 | |
10 | 21/05/2017 22:38:45 | 0 | 0 | |
11 | 21/05/2017 22:38:50 | 0 | 0 | |
12 | 21/05/2017 22:38:55 | 0 | 0 | |
13 | 21/05/2017 22:39:00 | 50 | 1 | 2 |
14 | 21/05/2017 22:39:05 | 50 | 1 | 2 |
15 | 21/05/2017 22:39:10 | 50 | 1 | 2 |
16 | 21/05/2017 22:39:15 | 0 | 0 | |
17 | 21/05/2017 22:39:20 | 0 | 0 | |
18 | 21/05/2017 22:39:25 | 0 | 0 | |
19 | 21/05/2017 22:39:30 | 50 | 1 | 3 |
20 | 21/05/2017 22:39:35 | 50 | 1 | 3 |
21 | 21/05/2017 22:39:40 | 50 | 1 | 3 |
22 | 21/05/2017 22:39:45 | 50 | 1 | 3 |
23 | 21/05/2017 22:39:50 | 50 | 1 | 3 |
24 | 21/05/2017 22:39:55 | 0 | 0 | |
25 | 21/05/2017 22:40:00 | 0 | 0 |
Solved! Go to Solution.
I have a proposed solution to your issue.
What I did was to put the logic into the Query Editor, where I have been able to know when the Pump is starting, running and off.
Based on the above I put in a 1 when the Pump is Starting and the rest were 0.
So then I just created a measure called [Pump Count] which shows as expected.
You can get the file here showing how I did this.
Power BI - Incremental Total based on Pump Times.pbix
Can I ask why the incremental count in your table?
If you are waning to count how often the pump was on for, you can do this once you have loaded the data by creating a measure.
Example Output = DISTINCTCOUNT('TableName'[Pump on])
With your example below the above measure will give you a total of 3 times the pump was on.
Hi @GilbertQ
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.
Ok thanks for the clarification.
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.
Thanks for your continuing help @GilbertQ
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.
I have a proposed solution to your issue.
What I did was to put the logic into the Query Editor, where I have been able to know when the Pump is starting, running and off.
Based on the above I put in a 1 when the Pump is Starting and the rest were 0.
So then I just created a measure called [Pump Count] which shows as expected.
You can get the file here showing how I did this.
Power BI - Incremental Total based on Pump Times.pbix
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |