Filtering a measure by value

[sirros_iot]

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)
                   VAR TS    = [.MaxTS] - ( INT( [.MaxTS] / 86400) * 86400)

Example [.MinAT]                                                                                                     

.MinAT = CALCULATE(

                                   MIN( RealTimeData[_at]);                                                                            THIS id NEEDS TO BE THE SAME...

                                                                           FILTER( RealTimeData; RealTimeData[id] = MAX( RealTimeData[id]) ) )

Example [.MaxTS]

.MaxTS = CALCULATE(

                                   MAX( RealTimeData[_ts]);                                                                            AS THIS id

                                                                           FILTER( RealTimeData; RealTimeData[id] = MAX( RealTimeData[id]) ) )

I need to introduce a calculation into one table, but I need to make it by Id, example. Thanks 

Id            MeasureA            RealTimeData[_at]             RealTimeData[_ts]

1                    15                              40                                      45

1                    15                              36                                      40

2                     8                               34                                      36

1                    15                              30                                      34

2                     8                               25                                      30

OBS: This measure calculates timestamp values.

I would be very grateful if someone would help me

Best Regards,

Diego

[sirros_iot]

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]

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]

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

[v-jiascu-msft]

Hi Diego,

Actually, some calculated columns still can be created in the Direct Query mode (not the live connection). You can try to convert [.CiclTime] to a calculated column.

The second approach could be using a measure like below. ([.CiclTime] is the measure)

Measure =
AVERAGEX (
    SUMMARIZE (
        'table',
        'table'[id],
        'table'[_ts],
        'table'[_at],
        "CiclTime", [.CiclTime]
    ),
    [Cicltime]
)

Best Regards,

Dale

[sirros_iot]

It isn't returning something..

Well, I'm getting the data by streaming

It is portuguese..

[v-jiascu-msft]

Hi @sirros_iot,

Which approach returned nothing? 

Did you connect to the Power BI Service datasets in the Desktop? The calculated column won't work in this type of connection. 

How about creating a table visual with only three columns and one measure?

[id]  [_ts]  [_at]  [measure]

Best Regards,

Dale

[sirros_iot]

Hi @v-jiascu-msft,

The last one.. But I'm revising it cause I think I did it wrong.

Yes, I connected to the Power BI Services datasets in the Power BI Desktop..

This is the historical data analysis button, this is my data origin

I am trying to find out how it can work, because I have a measure of the difference between AT and TS(MeasureA), and..

MeasureA = SUM('Table'[_ts]) - SUM('Table'[_at])

So, my new measure creates a virtual table with other measure.. It is ok? It didn't work when I triyed but I'm searching for a mistake

Thank you for the reply,

Best Regards,

Diego

[sirros_iot]

Hi @v-jiascu-msft,

Do you think I can do something with this data? I need to see if I have to request changes in the project

Thanks,

Best Regards,

Diego

[sirros_iot]

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