Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
procurtstinate
Frequent Visitor

Assign incremental int to blocks of data

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:

 

IndexTimestampPump 1 OutputPump onExample Output
121/05/2017 22:38:0000 
221/05/2017 22:38:0500 
321/05/2017 22:38:105011
421/05/2017 22:38:155011
521/05/2017 22:38:205011
621/05/2017 22:38:255011
721/05/2017 22:38:305011
821/05/2017 22:38:3550 
921/05/2017 22:38:4000 
1021/05/2017 22:38:4500 
1121/05/2017 22:38:5000 
1221/05/2017 22:38:5500 
1321/05/2017 22:39:005012
1421/05/2017 22:39:055012
1521/05/2017 22:39:105012
1621/05/2017 22:39:1500 
1721/05/2017 22:39:2000 
1821/05/2017 22:39:2500 
1921/05/2017 22:39:305013
2021/05/2017 22:39:355013
2121/05/2017 22:39:405013
2221/05/2017 22:39:455013
2321/05/2017 22:39:505013
2421/05/2017 22:39:5500 
2521/05/2017 22:40:0000 
1 ACCEPTED SOLUTION

hi @procurtstinate

 

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.

 

A-PBI Community.png


You can get the file here showing how I did this.

 

Power BI - Incremental Total based on Pump Times.pbix

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

7 REPLIES 7
GilbertQ
Super User
Super User

Hi @procurtstinate

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.

 

Capture.PNG

 

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.

Hi @procurtstinate

 

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.

 

A-PBI Community.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.

hi @procurtstinate

 

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.

 

A-PBI Community.png


You can get the file here showing how I did this.

 

Power BI - Incremental Total based on Pump Times.pbix

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

That did the trick!

Thank you very much for your help @GilbertQ

Awesome. Thanks for letting me know




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.