cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

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

9 REPLIES 9
Helper IV

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.

Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

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

Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

It isn't returning something..

Well, I'm getting the data by streaming

It is portuguese..

Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

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

Best Regards,

Diego

Helper IV

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

Helper IV

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.