Forum Discussion

sirros_iot's avatar
sirros_iot
Helper III
8 years ago

Filtering a measure by value

Hello all!   I have this seaction on a measure... And I would like to get this values by the same Id Measure A =                     VAR ANT = [.MinAT] - ( INT( [.MinAT] / 86400) * 86400)       ...
Need Help
sirros_iot's avatar
sirros_iot
Helper III
8 years ago

Trying to explain better...

I have this measure that gives me the difference between two timestamps(time in seconds) values ... RealTimeData[_at] and RealTimeData[_ts]

.DiffPeriodTest = 
    VAR MinAT = CALCULATE(MIN(RealTimeData[_at]);FILTER(RealTimeData;RealTimeData[id]=MAX(RealTimeData[id])))
    VAR MaxTS = CALCULATE(MAX(RealTimeData[_ts]);FILTER(RealTimeData;RealTimeData[id]=MAX(RealTimeData[id])))
    VAR ANT = MinAT - (INT(MinAT/86400)*86400)
    VAR TS  = MaxTS - (INT(MaxTS/86400)*86400)
    
    VAR FIRST = IF( ANT < 7200; (- ANT + 86400) - 21600;
                    IF( ANT < 39600; (- ANT + 86400) - 18000;
                        IF( ANT < 64800; (- ANT + 86400) - 12600;
                            (- ANT + 86400)
                        )
                    )
    )
    VAR LAST = IF( TS > 77400; TS - 21600;
                    IF( TS > 46800; TS - 9000;
                        IF( TS > 11000; TS - 3600;
                            TS)
                    )
    )
    RETURN
    IF( (INT([.MaxTS]/86400)*86400) < [.MaxTS] && (INT([.MaxTS]/86400)*86400) < [.MinAT];
                IF( ((39600 > TS) && (TS > 11000)) && (ANT < 7200); TS - ANT - 3600;
                    IF( ((64800 > TS) && (TS > 46800)) && ((11000 < ANT) && (ANT < 39600)); TS - ANT - 5400;
                        IF( (TS > 77400) && ((46800 < ANT) && (ANT < 64800)); TS - ANT - 12600;
                            
                            IF( (ANT < 7200) && ((64800 > TS) && (TS > 46800)); TS - ANT - 9000;
                                IF( (ANT < 7200) && (TS > 77400); TS - ANT - 21600;
                                    TS - ANT)
                            )
                        )
                    )
                );
       IF( (INT([.MaxTS]/86400)*86400) < [.MaxTS] && (INT([.MaxTS]/86400)*86400-86400 < [.MinAT] && [.MinAT] < INT([.MaxTS]/86400)*86400); (FIRST + LAST);
              (INT([.MaxTS]/86400) - INT([.MinAT]/86400))*(86400-21600)+(FIRST + LAST)
           )
         )

All this calculation is discounting the break time, like..
-21600
-9000
-3600

And 86400 is equal to one day in seconds.
Sorry, I don't know how to make it clear to understand...
This is the shown measure named MeasureA in the main question, and as you can see, I would like to calculate it by Id.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Diego,

     

    Can you share a sample of your original data AND the expected result? To be honest, I don't  know what is expected.

     

    Best Regards,

    Dale

    • sirros_iot's avatar
      sirros_iot
      Helper III
      8 years ago

      Hello Dale! Unfortunately the firm doesn't have a pro version..

       

      Actually, I was expecting that reply... It's a little bit complicated to explain all the situation. I'm getting the data by direct query, so, I can't use calculated columns and tools like that. I'm trying to do all in measures.

       

      This is how I receive the data

      I call cycle each line..

      sensor = arduino  ||  id = order id || ciclos = total number of cycles by id || contagem = score || _at = timestamp(number in seconds since 1970) from the beginning of the cycle ||  _ts = timestamp from the end of the cycle. 

      I can do things like... With the dot(.) is measure

       

       My issue.. How to get the average time of each id? It would be an average apllied at the [.CiclTime]

       

      Thank you for the reply, sorry anything, any mistake

      Best Regards,

      Diego

      • sirros_iot's avatar
        sirros_iot
        Helper III
        8 years ago

        There is no id numbers yet.

         

        Let's say that is showing like..

         

        id

        1

        1

        1

        2

        2

        1

        2

        2

        3

        3

         

        Something like this..

         

        Thanks