DAX: calculating maximum period of LSA non-compliance from individual probe readings

The Problem

The contractor has to maintain the values of n probes under a certain threshold.
Data is measured each 5 minutes, 24 hours per day.
An incident is defined as the period while a probe readings are continuously over the threshold
LSA is based on the number of incidents in a month, and the maximum period of an incident

In the following PowerBI page we can see an incident for probe CPD_TEMP03 , when the threshold is set at level 25

Although it is trivial to know if at a certain time the value is over the threshold, it will require some tweaking with the data to know the duration of the corresponding incident. We will build the following measures:
IsIncident (boolean): when Average(value) is over the Threshold
Duration of incident (time): for an incident, time difference between last reading without incident and current reading, calculated at the original table level
Greatest duration of incident (time): for a period, the greatest value of the previous measure

We can expect performance problems, since we will have to iterate through the Temperature Readings table, for each row of that table for calculating Duration of Incident, and once again to find Greatest duration of incident. That is, Number of Rows * Number of Rows * Number of Rows. For each probe, we have up to 12values/hour * 24hours/day * 31 day/month = 8929 readings/month , which means traversing 8929 ^3 = 711.643.594.752, rather a big number 🙁

                                                             711.643.594.752


I will add some measures as follows:

Is Over Threshold = AVERAGE(‘Temperature Readings'[Value]) > Threshold[Threshold Value]

Time since tripping =
var CurrentDateTime = AVERAGE(‘Calendar'[Date/Time])
var FirstDateT =CALCULATE(min(‘Calendar'[Date/Time]), ALLSELECTED(‘Calendar’))
var LastDateT = CALCULATE(mAX(‘Calendar'[Date/Time]), ALLSELECTED(‘Calendar’))
var Readings = CALCULATE(COUNT(‘Calendar'[Date/Time]), ALLSELECTED(‘Calendar’))
var IsFirstReadingTripped = CALCULATE([Is Over Threshold],’Calendar'[Date/Time] = FirstDateT)
var interval = DIVIDE(DATEDIFF(FirstDateT,LastDateT,MINUTE), Readings -1)
var LastTimeBeforeTripping =
IF(
IsFirstReadingTripped,
FirstDateT – interval/60/24,
CALCULATE(
max(‘Calendar'[Date/Time]) ,
Filter( ALL(‘Calendar’), ‘Calendar'[Date/Time] <= CurrentDateTime && CALCULATE([Is Over Threshold]) = FALSE() ) ) )
var duration = IF( HASONEVALUE(‘Calendar'[Date/Time]),
datediff(LastTimeBeforeTripping,CurrentDateTime, MINUTE)
)
return duration

Max Duration of Incident =
MAXX(
ALLSELECTED(‘Calendar’),
CALCULATE([Time since tripping])
)

For a period of 4 days, in my PC (i7, 32Gb), it takes about 2 seconds to construct the table as shown

Not so bad. Let’s see what happens when we put a month worth of data

For a week of data, perfomance drops to:

For two weeks, I get

For 28 days, even worse, as expected: Now it is not a good idea to leave the dates selector open to any range of days anymore 🙁

Can you find a better way to calculate this measure?

You may download the example file from here

Leave a Comment

DAX: calculo de duración máxima de una incidencia a partir de una tabla de mediciones periódicas, con umbral variable.

El Problema

El contratista debe mantener un control de temperatura sobre n sensores. El límite de aceptación (umbral) se debe poder ajustar desde el propio informe.
Los sensores anotan datos cada 5 minutos, 24 horas por día.
Se define como incidente cada período durante el cual los valores de un sensor están por encima del umbral definido.
El LSA estará basado en la duración máxima de una incidencia, en un cierto período (semana, mes)

En la siguiente imagen, vemos los valores que una cierta sonda a sufrido y la incidencia durante un período de 25 minutos correspondiente, si el umbral se define con valor 25

Saber si en un momento dado, está ocurriendo una incidencia es trivial, pero poder determinar la duración de la incidencia requiere profundizar en DAX.
Construyo las siguientes medidas:
IsIncident (boolean): cuando Average(value) está por encima del Threshold (umbral)
Duration of incident (time): indica cuanto tiempo lleva la incidencia en curso
Greatest duration of incident (time): Para todas las lecturas con incidencia, la de mayor duración en curso

Es de esperar problemas de rendimioento en las fórmulas DAX, ya que este tipo de cálculos requerirá iterar por la tabla de las temperaturas medidas, para localizar el inicio de una incidencia, y nuevamente para encontrar la duración máxima. Si esto lo hacemos para una tabla en la que vemos cada lectura, entonces tendremos que recorrer el número de registros elevado al cubo. En nuestro caso, para cada sonda, 12valores/hora * 24horas/día * 31 día/mes = 8929 lecturas por mes, DAX recorrerá 8929 ^3 = 711.643.594.752 registros, lo que no es poco 🙁

                                                             711.643.594.752


Las medidas a crear serán:

Is Over Threshold = AVERAGE(‘Temperature Readings'[Value]) > Threshold[Threshold Value]

Time since tripping =
var CurrentDateTime = AVERAGE(‘Calendar'[Date/Time])
var FirstDateT =CALCULATE(min(‘Calendar'[Date/Time]), ALLSELECTED(‘Calendar’))
var LastDateT = CALCULATE(mAX(‘Calendar'[Date/Time]), ALLSELECTED(‘Calendar’))
var Readings = CALCULATE(COUNT(‘Calendar'[Date/Time]), ALLSELECTED(‘Calendar’))
var IsFirstReadingTripped = CALCULATE([Is Over Threshold],’Calendar'[Date/Time] = FirstDateT)
var interval = DIVIDE(DATEDIFF(FirstDateT,LastDateT,MINUTE), Readings -1)
var LastTimeBeforeTripping =
IF(
IsFirstReadingTripped,
FirstDateT – interval/60/24,
CALCULATE(
max(‘Calendar'[Date/Time]) ,
Filter( ALL(‘Calendar’), ‘Calendar'[Date/Time] <= CurrentDateTime && CALCULATE([Is Over Threshold]) = FALSE() ) ) )
var duration = IF( HASONEVALUE(‘Calendar'[Date/Time]),
datediff(LastTimeBeforeTripping,CurrentDateTime, MINUTE)
)
return duration

Max Duration of Incident =
MAXX(
ALLSELECTED(‘Calendar’),
CALCULATE([Time since tripping])
)

Para datos de 4 días, en mi PC (i7, 32Gb) el cálculo se resuelve en unos dos segundos.

No está mal, pero carguemos datos para un mes completo, y veamos que sucede

Para datos de siete días:

Para quince días:

Por suerte febrero tiene solo 28 días, y el cálculo de la tabla lleva casi dos minutos
Quizá debamos quitar la tabla, y quedarnos solo con el indicador (gauge), e inclusive quitar el segmentador por fechas de la página 🙁

¿Conoces otra forma de resolver este problema, en forma más simple o con mayor rendimiento?

Puedes descargar el fichero de ejemplo desde aquí

Leave a Comment