Filtering a measure by value

Question

Hello all!&nbsp;I have this seaction on a measure... And I would like to get this values by the same IdMeasure A =&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;VAR ANT = [.MinAT] - ( INT( [.MinAT] / 86400) * 86400)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;VAR TS&nbsp; &nbsp; = [.MaxTS] - ( INT( [.MaxTS] / 86400) * 86400)&nbsp;Example [.MinAT]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.MinAT = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MIN( RealTimeData[_at]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THIS id NEEDS TO BE THE SAME...&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER( RealTimeData; RealTimeData[id] = MAX( RealTimeData[id]) ) )Example [.MaxTS].MaxTS&nbsp;= CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MAX( RealTimeData[_ts]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS THIS id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER( RealTimeData; RealTimeData[id] = MAX( RealTimeData[id]) ) )&nbsp;I need to introduce a&nbsp;calculation into one table, but I need to make it by Id, example. Thanks&nbsp;&nbsp;Id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MeasureA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RealTimeData[_at]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;RealTimeData[_ts]1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 451&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 36&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 402&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;34&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;361&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;342&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;30&nbsp;OBS: This measure calculates timestamp values.I would be very grateful if someone would help me&nbsp;Best Regards,Diego

v-jiascu-msft · Answer

Hi&nbsp;Diego,
&nbsp;
Can you share a sample of your original data AND the expected result? To be honest, I don't&nbsp; know what is expected.
&nbsp;
Best Regards,
Dale

sirros_iot · Answer

Trying to explain better...I have this measure that gives me the difference between two timestamps(time in seconds) values ... RealTimeData[_at] and&nbsp;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 &lt; 7200; (- ANT + 86400) - 21600;
                    IF( ANT &lt; 39600; (- ANT + 86400) - 18000;
                        IF( ANT &lt; 64800; (- ANT + 86400) - 12600;
                            (- ANT + 86400)
                        )
                    )
    )
    VAR LAST = IF( TS &gt; 77400; TS - 21600;
                    IF( TS &gt; 46800; TS - 9000;
                        IF( TS &gt; 11000; TS - 3600;
                            TS)
                    )
    )
    RETURN
    IF( (INT([.MaxTS]/86400)*86400) &lt; [.MaxTS] &amp;&amp; (INT([.MaxTS]/86400)*86400) &lt; [.MinAT];
                IF( ((39600 &gt; TS) &amp;&amp; (TS &gt; 11000)) &amp;&amp; (ANT &lt; 7200); TS - ANT - 3600;
                    IF( ((64800 &gt; TS) &amp;&amp; (TS &gt; 46800)) &amp;&amp; ((11000 &lt; ANT) &amp;&amp; (ANT &lt; 39600)); TS - ANT - 5400;
                        IF( (TS &gt; 77400) &amp;&amp; ((46800 &lt; ANT) &amp;&amp; (ANT &lt; 64800)); TS - ANT - 12600;
                            
                            IF( (ANT &lt; 7200) &amp;&amp; ((64800 &gt; TS) &amp;&amp; (TS &gt; 46800)); TS - ANT - 9000;
                                IF( (ANT &lt; 7200) &amp;&amp; (TS &gt; 77400); TS - ANT - 21600;
                                    TS - ANT)
                            )
                        )
                    )
                );
       IF( (INT([.MaxTS]/86400)*86400) &lt; [.MaxTS] &amp;&amp; (INT([.MaxTS]/86400)*86400-86400 &lt; [.MinAT] &amp;&amp; [.MinAT] &lt; 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-3600And 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.

sirros_iot · Answer

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 · Answer

There is no id numbers yet.&nbsp;Let's say that is showing like..&nbsp;id1112212233&nbsp;Something like this..&nbsp;Thanks

v-jiascu-msft · Answer

Hi&nbsp;Diego,
&nbsp;
Actually, some calculated columns still can be created in the Direct Query mode (not the live connection). You can try to convert&nbsp;[.CiclTime] to a calculated column.
The second approach could be using a measure like below. ([.CiclTime] is the measure)
&nbsp;
Measure =
AVERAGEX (
    SUMMARIZE (
        'table',
        'table'[id],
        'table'[_ts],
        'table'[_at],
        "CiclTime", [.CiclTime]
    ),
    [Cicltime]
)

&nbsp;
Best Regards,
Dale
&nbsp;

Forum Discussion

Filtering a measure by value

9 Replies

Related content

Dynamic measure filtering

Measure returning incorrect value

Measure: FILTER([value] is not blank

Filter by Rank measure

Creating a measure that is the value of another measure but ignores a filter