Forum Discussion

numersoz's avatar
numersoz
Helper III
6 years ago
Solved

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 ther...
Need Help
Show and Tell
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    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" )

     

     

     

     

     

    Please refer to the pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERoNh4-nNQpEufq-8Nl3glIBjc0tCc3J6tF3UMOUVpSkUQ?e=otD0UZ

     

    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

     

  • numersoz's avatar
    numersoz
    Helper III
    6 years ago

    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.

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Community Support
      6 years ago

      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" )

       

       

       

       

       

      Please refer to the pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERoNh4-nNQpEufq-8Nl3glIBjc0tCc3J6tF3UMOUVpSkUQ?e=otD0UZ

       

      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