cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
numersoz
Helper III
Helper III

Flag Consecutive Values

Hi,

 

I have hourly timestamp sensor data. I would like to flag when a certain sensor value is below a certain threshold at least four consecutive hours within a day.

For example, 

Day 1 there were 11 values below the threshold but non of them are back to back. Thus day 1 would not get flagged.

On day 2, there were at least one occurance of 4 hours of back to back consecutive values below a set treshold so day 2 has to get flagged. 

How would I achvie this? If this was Python, I would have just done a four loop for every hour in a day and checking 3 values before and after each hour. If The value and 3 before/after also have this condition, I would flag the day. But on Power BI I am not sure how to achive this.

1 ACCEPTED SOLUTION

Hi @numersoz ,

 

Create a date column for your table:

 

Date = 'Table'[Time Stamp].[Date]

 

Then you can use the following measure :

 

Measure = 
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Time Stamp],'Table'[Date],'Table'[Value],
        "aa", IF (
            0
                IN CALCULATETABLE (
                    VALUES ( 'Table'[Value] ),
                    FILTER (
                'Table',
                        'Table'[Time Stamp]
                            >= EARLIER ( 'Table'[Time Stamp] ) - 1 / 8
                            && 'Table'[Time Stamp] <= EARLIER('Table'[Time Stamp] )
                            && 'Table'[Date] = EARLIER('Table'[Date])
                    )
                ),
            1,
            0
        )
    )
RETURN
    IF ( COUNTX ( FILTER ( t, [aa] = 0 &&COUNTROWS(t)>=4),[aa]) > 0, "flagged", "unflagged" )

 

 

Capture1.PNG

 

 

 

Please refer to the pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERoNh4-nNQpEufq-8N...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

View solution in original post

5 REPLIES 5
v-deddai1-msft
Community Support
Community Support

Hi @numersoz ,

 

Would you please inform us more detailed information( your  data(by OneDrive for Business)) if possible? Then we will help you more correctly.

 

Please do mask sensitive data before uploading.

 

Thanks for your understanding and support.

 

Best Regards,

Dedmon Dai

Hi, @v-deddai1-msft 

Thank you for your support. Please see below a dummy data for 2 days of time stamp.

For each day, I want to flag when value is between 1-20%. Within any day, if there are four consecutive values that has the flag of being between 1-20%, I need to flag that day.

 

This way instead of looking into hunders of days, I know that I have a problem in that day and it needs to be looked at.

Thanks

Time StampValue
1/1/2019 0:000%
1/1/2019 1:002%
1/1/2019 2:002%
1/1/2019 3:005%
1/1/2019 4:000%
1/1/2019 5:005%
1/1/2019 6:005%
1/1/2019 7:000%
1/1/2019 8:006%
1/1/2019 9:0090%
1/1/2019 10:0060%
1/1/2019 11:001%
1/1/2019 12:000%
1/1/2019 13:002%
1/1/2019 14:005%
1/1/2019 15:005%
1/1/2019 16:006%
1/1/2019 17:000%
1/1/2019 18:000%
1/1/2019 19:000%
1/1/2019 20:007%
1/1/2019 21:008%
1/1/2019 22:009%
1/1/2019 23:009%
1/2/2019 0:000%
1/2/2019 1:001%
1/2/2019 2:001%
1/2/2019 3:001%
1/2/2019 4:001%
1/2/2019 5:000%
1/2/2019 6:001%
1/2/2019 7:001%
1/2/2019 8:001%
1/2/2019 9:000%
1/2/2019 10:000%
1/2/2019 11:000%
1/2/2019 12:005%
1/2/2019 13:005%
1/2/2019 14:006%
1/2/2019 15:000%
1/2/2019 16:0080%
1/2/2019 17:0089%
1/2/2019 18:009%
1/2/2019 19:0099%
1/2/2019 20:009%
1/2/2019 21:000%
1/2/2019 22:000%
1/2/2019 23:009%
1/3/2019 0:009%



Greg_Deckler
Super User IV
Super User IV

@numersoz  - You would probably want to create a new column and use EARLIER to check the previous row's value compared to the current row's value. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Hi @Greg_Deckler 

 

Very interesting topic that can actually be useful to me for something else!

Now you gave me the idea to use EARLIER() instead. What if for each row I did a count, I check the value itself, (1, 2, 3) before, (1, 2, 3) after and tested if each were with the value of 0.01 - 0.20. And I added number of times that it meets the criteria.

This would be my calculated column. If the value of this calculated column is greater than four, it meets my criteria of having four consecutive values.

But here is another issue, I have 1 million rows. I just tried to count the one before to give it a shot.

count_column = COUNTX(DATA, 
CONVERT(IF(EARLIER(DATA[Load], 1) > 0.1 & EARLIER(DATA[Load], 1) > 0.2), INTEGER))

But it took about 5 minutes to calculate. Now obviously something is wrong as it gave me value of 1 million (my row count). But even if I fix it, I feel like doing this for each for could take long time and give performance issues.

Hi @numersoz ,

 

Create a date column for your table:

 

Date = 'Table'[Time Stamp].[Date]

 

Then you can use the following measure :

 

Measure = 
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Time Stamp],'Table'[Date],'Table'[Value],
        "aa", IF (
            0
                IN CALCULATETABLE (
                    VALUES ( 'Table'[Value] ),
                    FILTER (
                'Table',
                        'Table'[Time Stamp]
                            >= EARLIER ( 'Table'[Time Stamp] ) - 1 / 8
                            && 'Table'[Time Stamp] <= EARLIER('Table'[Time Stamp] )
                            && 'Table'[Date] = EARLIER('Table'[Date])
                    )
                ),
            1,
            0
        )
    )
RETURN
    IF ( COUNTX ( FILTER ( t, [aa] = 0 &&COUNTROWS(t)>=4),[aa]) > 0, "flagged", "unflagged" )

 

 

Capture1.PNG

 

 

 

Please refer to the pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERoNh4-nNQpEufq-8N...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors