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
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
Super User

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

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.