Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have read through many of the other messages, but I can't seem to get this one to work.
I have sensor data (Please, find it posted) and want to create two measures over 24 hours:
1-) The first measure is a rolling a verage of sensor values over 24 hours
2-) The second measure is a count of the sensor values 24 hours rolling average which exceeded 4. That is, I want to count only those sensor values of the newly created measure that exceeded 4. If there is no exceedance, it should return 0.
Thank you 🙂
Date /Time Sensor Values
01.01.2021 00:00 | 0,38 |
01.01.2021 01:00 | 0,30 |
01.01.2021 02:00 | 0,30 |
01.01.2021 03:00 | 0,31 |
01.01.2021 04:00 | 0,31 |
01.01.2021 05:00 | 0,32 |
01.01.2021 06:00 | 0,32 |
01.01.2021 07:00 | 0,31 |
01.01.2021 08:00 | 0,31 |
01.01.2021 09:00 | 0,31 |
01.01.2021 10:00 | 0,30 |
01.01.2021 11:00 | 0,31 |
01.01.2021 12:00 | 0,31 |
01.01.2021 13:00 | 0,32 |
01.01.2021 14:00 | 0,33 |
01.01.2021 15:00 | 0,33 |
01.01.2021 16:00 | 0,34 |
01.01.2021 17:00 | 0,33 |
01.01.2021 18:00 | 0,33 |
01.01.2021 19:00 | 0,34 |
01.01.2021 20:00 | 0,35 |
01.01.2021 21:00 | 0,35 |
01.01.2021 22:00 | 0,34 |
01.01.2021 23:00 | 0,34 |
02.01.2021 00:00 | 0,34 |
02.01.2021 01:00 | 0,33 |
02.01.2021 02:00 | 0,33 |
02.01.2021 03:00 | 0,32 |
02.01.2021 04:00 | 0,32 |
02.01.2021 05:00 | 0,32 |
02.01.2021 06:00 | 0,33 |
02.01.2021 07:00 | 0,34 |
02.01.2021 08:00 | 0,34 |
02.01.2021 09:00 | 0,34 |
02.01.2021 10:00 | 0,35 |
02.01.2021 11:00 | 0,35 |
02.01.2021 12:00 | 0,35 |
02.01.2021 13:00 | 0,35 |
02.01.2021 14:00 | 0,35 |
02.01.2021 15:00 | 0,37 |
02.01.2021 16:00 | 0,37 |
02.01.2021 17:00 | 0,37 |
02.01.2021 18:00 | 0,37 |
02.01.2021 19:00 | 0,35 |
02.01.2021 20:00 | 0,36 |
02.01.2021 21:00 | 0,36 |
02.01.2021 22:00 | 0,36 |
02.01.2021 23:00 | 0,35 |
03.01.2021 00:00 | 0,34 |
03.01.2021 01:00 | 0,35 |
03.01.2021 02:00 | 0,35 |
03.01.2021 03:00 | 0,35 |
03.01.2021 04:00 | 0,33 |
03.01.2021 05:00 | 0,32 |
03.01.2021 06:00 | 0,33 |
03.01.2021 07:00 | 0,33 |
03.01.2021 08:00 | 0,31 |
03.01.2021 09:00 | 0,30 |
03.01.2021 10:00 | 0,29 |
03.01.2021 11:00 | 0,30 |
03.01.2021 12:00 | 0,29 |
03.01.2021 13:00 | 0,29 |
03.01.2021 14:00 | 0,29 |
03.01.2021 15:00 | 0,30 |
03.01.2021 16:00 | 0,29 |
03.01.2021 17:00 | 0,30 |
03.01.2021 18:00 | 0,29 |
03.01.2021 19:00 | 0,29 |
03.01.2021 20:00 | 0,29 |
03.01.2021 21:00 | 0,29 |
03.01.2021 22:00 | 0,28 |
03.01.2021 23:00 | 0,28 |
04.01.2021 00:00 | 0,28 |
04.01.2021 01:00 | 0,27 |
04.01.2021 02:00 | 0,26 |
04.01.2021 03:00 | 0,27 |
04.01.2021 04:00 | 0,27 |
04.01.2021 05:00 | 0,29 |
04.01.2021 06:00 | 0,30 |
04.01.2021 07:00 | 0,31 |
04.01.2021 08:00 | 0,32 |
04.01.2021 09:00 | 0,32 |
04.01.2021 10:00 | 0,32 |
04.01.2021 11:00 | 0,33 |
04.01.2021 12:00 | 0,32 |
04.01.2021 13:00 | 0,33 |
04.01.2021 14:00 | 0,43 |
04.01.2021 15:00 | 0,34 |
04.01.2021 16:00 | 0,34 |
04.01.2021 17:00 | 0,32 |
04.01.2021 18:00 | 0,31 |
04.01.2021 19:00 | 0,31 |
04.01.2021 20:00 | 0,28 |
04.01.2021 21:00 | 0,29 |
04.01.2021 22:00 | 0,29 |
04.01.2021 23:00 | 0,29 |
05.01.2021 00:00 | 0,51 |
05.01.2021 01:00 | 0,28 |
05.01.2021 02:00 | 0,29 |
05.01.2021 03:00 | 0,29 |
05.01.2021 04:00 | 0,30 |
05.01.2021 05:00 | 0,30 |
05.01.2021 06:00 | 0,31 |
05.01.2021 07:00 | 0,31 |
05.01.2021 08:00 | 0,32 |
05.01.2021 09:00 | 0,32 |
05.01.2021 10:00 | 0,31 |
05.01.2021 11:00 | 0,31 |
05.01.2021 12:00 | 0,31 |
05.01.2021 13:00 | 0,30 |
05.01.2021 14:00 | 0,30 |
05.01.2021 15:00 | 0,33 |
05.01.2021 16:00 | 0,32 |
05.01.2021 17:00 | 0,33 |
05.01.2021 18:00 | 0,33 |
05.01.2021 19:00 | 0,33 |
05.01.2021 20:00 | 0,32 |
05.01.2021 21:00 | 0,32 |
05.01.2021 22:00 | 0,32 |
05.01.2021 23:00 | 0,30 |
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, here's my solution.
Create two measures.
Average =
AVERAGEX (
FILTER (
ALL ( 'Table' ),
DATEVALUE ( 'Table'[Date /Time] ) = DATEVALUE ( MAX ( 'Table'[Date /Time] ) )
&& 'Table'[Date /Time] <= MAX ( 'Table'[Date /Time] )
),
'Table'[Sensor Values]
)
Count = COUNTX ( FILTER ( ALL ( 'Table' ), [Average] > 4 ), 'Table'[Date /Time] )
In my sample, I modify the value of 1/1/21 23:00 to 100, and the count get 1.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, here's my solution.
Create two measures.
Average =
AVERAGEX (
FILTER (
ALL ( 'Table' ),
DATEVALUE ( 'Table'[Date /Time] ) = DATEVALUE ( MAX ( 'Table'[Date /Time] ) )
&& 'Table'[Date /Time] <= MAX ( 'Table'[Date /Time] )
),
'Table'[Sensor Values]
)
Count = COUNTX ( FILTER ( ALL ( 'Table' ), [Average] > 4 ), 'Table'[Date /Time] )
In my sample, I modify the value of 1/1/21 23:00 to 100, and the count get 1.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try
Avg Measure =calculate(Average(Table[Sensor Values]), filter(table, table[datetime] <=now() && table[datetime] >=now() -time(24,0,0)))
Count GT 4 = calculate(count(Table[Sensor Values]), filter(table, table[datetime] <=now() && table[datetime] >=now() -time(24,0,0) && Table[Sensor Values] >4 ))
Thank you for your reply 🙂
I tried these measures, but the date/time disappears when I drag the Avg measure to the table visualisation(see photos attached), any idea?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |