Análisis (duración, recuento, etc.) de una gran cantidad de marcas de tiempo para una máquina

[Syndicate_Admin]

Hola a todos. Soy un novato en Power BI, pero estoy aprendiendo más todos los días gracias a este foro. Me disculpo si hago preguntas obvias/ fáciles.

Ojective: Me gustaría utilizar los datos que se describen a continuación para calcular cuántos ciclos de encendido y apagado tiene una máquina en un período determinado, así como un análisis de la duración (es decir, tiempo promedio de "encendido", tiempo máximo / mínimo de encendido", etc.). Básicamente, quiero ser capaz de tendencia de estos parámetros con el tiempo y averiguar cuándo / si cambian. Además, hay tres máquinas idénticas y me gustaría comparar cada una de sus tendencias.

Datos sin procesar: Tengo una hoja de cálculo de varios cientos de miles de filas que muestra datos para cuando una máquina está en y cuando está desactivada. Cada vez que la máquina se enciende o apaga, hay una marca de tiempo correspondiente; sin embargo, a veces también hay "marcas de tiempo intermedias" cuando el estado de la máquina no cambia con respecto a la marca de tiempo anterior, pero se registra sin embargo. La tabla de datos sin procesar tiene un aspecto similar al siguiente:

Timestamp		Estado
1/23/2012 13:00:12		En
23/01/2012 13:14:11		Apagado
1/24/2012 12:44:11 PM		Apagado
1/25/2012 12:10:12 PM		Apagado
1/26/2012 11:40:12 SOY		Apagado
1/26/2012 23:20:11		En
1/26/2012 23:44:11		Apagado
1/27/2012 12:02:12 SOY		En
1/27/2012 12:26:12 SOY		Apagado

Producto final deseado: Me gustaría poder crear una visualización / panel de control que muestre para un mes / trimestre / año en particular el número de ciclos de encendido y apagado, así como la duración promedio / mínimo / máximo en el que estaba la máquina. Una vez más, en realidad hay tres máquinas para las que tengo estos datos y otra cosa que me gustaría ver / calcular es cuándo (y por cuánto tiempo) están "Encendidos" al mismo tiempo (así como una comparación de las tendencias generales generales).

Mi conjetura sobre por dónde empezar: si los datos se compusieran de solo puntos en los que la máquina se encendiera o apagara, parece que sería bastante sencillo decirle a Power BI que creara un nuevo colum que tomaría la marca de tiempo de la línea actual y subract la línea anterior. Esto le daría cuánto tiempo la máquina estuvo en ese estado respectivo (Encendido o Apagado) y luego podría filtrar por estado y Mes / Trimestre / Año. Dado que hay "marcas de tiempo intermedias" donde hay un punto de datos pero el estado de la máquina no ha cambiado con respecto a la marca de tiempo anterior, supongo que necesitaré que Power BI tome la marca de tiempo de la línea de datos actual y filtre los datos remaing para mostrar solo las marcas de tiempo que son posteriores Y tienen un estado diferente.

Cualquier ayuda es apreciada. Gracias.

[Syndicate_Admin · Accepted solution]

No estoy seguro de si ve varios "On" en una fila (sus datos solo muestran "Off" con eso), pero creo que esto también debería manejar ese escenario. Esta es una expresión de columna que devuelve el tiempo en minutos hasta el siguiente Off y omite cualquier duplicado de "On" en el medio. Usted debe ser capaz de promediar / min / max esta columna para obtener los resultados deseados.

En duración =
DÓNDE thisDT = 'Estado'[Marca de tiempo]
DÓNDE nextoff =
CALCULAR (
MIN ( 'Estado'[Marca de tiempo] ),
ALLEXCEPT ( 'Status', 'Status'[MachineNumber] ),
'Estado'[Marca de tiempo] > thisDT,
'Estado'[Estado] = "Desactivado"
)
DÓNDE prevon =
CALCULAR (
MÁXIMO ( 'Estado'[Marca de tiempo] ),
ALLEXCEPT ( 'Status', 'Status'[MachineNumber] ),
'Status'[Marca de tiempo] < thisDT,
'Estado'[Estado] = "Activado"
)
DÓNDE checkduplicateon =
ISBLANK (
CALCULAR (
MIN ( 'Estado'[Marca de tiempo] ),
ALLEXCEPT ( 'Status', 'Status'[MachineNumber] ),
'Status'[Marca de tiempo] < thisDT
&& 'Estado'[Marca de tiempo] > prevon,
'Estado'[Estado] = "Desactivado"
)
)
DÓNDE isfirstrow =
thisDT
= CALCULAR (
MIN ( 'Estado'[Marca de tiempo] ),
ALLEXCEPT ( 'Status', 'Status'[MachineNumber] )
)
DEVOLUCIÓN
SI (
( isfirstrow || NO ( checkduplicateon ) )
&& 'Estado'[Estado] = "Activado",
DATEDIFF ( thisDT, nextoff, MINUTO )
)

Palmadita

[Syndicate_Admin]

Hola @mahoneypat

Tuve un problema muy similar y su solución realmente me ayudó, lo único ahora es que estoy teniendo un problema en el rendimiento ... se queda sin memoria si quiero calcular en mi conjunto de datos si hay demasiadas filas, para subconjuntos más pequeños está funcionando bien.

Así que me preguntaba si cambié las columnas con fecha y hora en dos columnas con fecha y hora si eso podría ayudar, pero no logré cambiar el código para que funcionara con esas dos columnas, ¿puede ayudarme con eso? Si tuviera que hacer lo mismo, pero si la columna Marca de tiempo se dividiera en columna de fecha y hora, ¿cómo se vería su código?

Obs: Ahora tengo alrededor de 240,000 filas (que teóricamente power bi puede manejar, pero he dicho que ha sido imposible manejar con esta fórmula dax)

[Syndicate_Admin]

Proporcione más información y algunos datos de muestra con la salida deseada en un formato que se pueda copiar / pegar. Este artículo / videos también pueden ayudar en las mejores prácticas para manejar la hora de la fecha.

Calcular y formatear duraciones en DAX – Hoosier BI

Palmadita

[Syndicate_Admin]

Primero intenté ejecutar esto como una columna calculada y una vez que la cantidad de datos aumentaba era imposible de procesar, así que lo adapté a una medida y funciona parcialmente. Una vez más, una vez que la cantidad de datos aumenta, no funciona. Se me agota el tiempo de espera.. También intenté evaluar el DAX en DAX studio para ver si podía mejorarlo, pero también recibo un error allí que dice "Se alcanzó el final de la entrada".

Tengo la misma situación explicada en el origen de la pregunta, necesito calcular cuánto tiempo un dispositivo está ENCENDIDO hasta la primera vez que está APAGADO, no el último ENCENDIDO.

DateTime	ID de dispositivo	Estado ON /OFF
2022-04-12 16:16	1	Apagado
2022-04-12 16:19	1	Apagado
2022-04-12 16:19	1	En
2022-04-12 16:19	1	En
2022-04-12 16:20	1	En
2022-04-12 16:20	1	En
2022-04-12 16:20	1	En
2022-04-12 16:20	1	En
2022-04-12 16:20	1	Apagado
2022-04-12 16:20	1	En
2022-04-12 16:24	1	Apagado
2022-04-13 07:22	1	Apagado
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:22	1	En
2022-04-13 07:25	1	Apagado

El DAX que tengo ahora se ve así:

TimeInState = 
var thisDT = SELECTEDVALUE(production[DateTime CET])
var nextOff = 
CALCULATE(
    MINX(production, production[DateTime CET]), 
    FILTER(
        ALLEXCEPT(production, production[Device ID]),
         production[DateTime CET] > thisDT),
         production[ON / OFF Status] = "OFF") 
         
var prevOn = 
CALCULATE(
    MAXX(production,production[DateTime CET]), 
    FILTER(
        ALLEXCEPT(production, production[Device ID]),
         production[DateTime CET] < thisDT),
          production[ON / OFF Status] = "ON") 

VAR checkduplication =
    ISBLANK (
        CALCULATE (
            MINX (production, 'production'[DateTime CET] ),
            FILTER(
                ALLEXCEPT ( 'production', 'production'[Device ID] ),
                'production'[DateTime CET] < thisDT
                && 'production'[DateTime CET] > prevon),
            'production'[ON / OFF Status] = "OFF"
        )
    )
VAR isfirstrow =
    thisDT
        = CALCULATE (
            MINX (production, 'production'[DateTime CET] ),
            ALLEXCEPT ( 'production', 'production'[PLC ID] )
        )

RETURN
    SWITCH(
        TRUE(),
        AND( isfirstrow || NOT ( checkduplication ),
        SELECTEDVALUE('production'[ON / OFF Status]) = "ON"),
        DATEDIFF ( thisDT, nextoff, SECOND )
    )

Y luego lo formateo así:

Total Operating Time = 
VAR TotalHours = DIVIDE(SUMX(production,[TimeInState]) , 3600)
VAR HoursInt = TRUNC(TotalHours)
VAR MinutesTotal = (TotalHours - HoursInt) *60
VAR MinutesInt = TRUNC(MinutesTotal)
VAR SecondsTotal = ROUND((MinutesTotal - MinutesInt)*60,0)
RETURN
    FORMAT(HoursInt, "00")&"h, "&FORMAT(MinutesInt, "00")&"m, "&FORMAT(SecondsTotal, "00")&"s "

[Syndicate_Admin]

Gracias @mahoneypat... esto parece funcionar para lo que estaba pidiendo. Pregunta de seguimiento rápido: si quiero que la duración se entregue en formato HH:MM en lugar de minutos, ¿tendría que escribir otra variable que divida por 60 y luego tome el resto decimal y lo converse de nuevo en minutos o habría una forma más simple?

[Syndicate_Admin]

Podría hacerlo como se describe con la función MOD. A continuación se muestra otro enfoque. Esto suma la nueva columna, divide por 3600 para convertirla en días en formato decimal, la convierte a DATETIME y, a continuación, la da formato como "hh:mm". Si eso va por encima de 24 horas, puede usar "d:hh:mm" para mostrar también el número de días.

TotalDuration2 = FORMAT(CONVERT(DIVIDE(SUM('Status'[On Duration]), 24*60), DATETIME), "hh:mm")

[Syndicate_Admin]

@mahoneypat Gracias, eso funciona. Última pregunta (creo): Mi hoja de cálculo es de 250k filas y cuando intento crear esta columna, Power BI, se me ahoga y me dice que no tiene suficiente memoria. Luego intenté hacer filas de 12k a la vez con la intención de exportar los resultados a Excel y luego repetir hasta que se realicen todas las etiquetas. Sin embargo, cuando exporto, parece que mi marca de tiempo cambia ligeramente de la que se importó (40888.3388484259 vs 40888.3388484144). ¿Alguna pista de por qué está sucediendo esto o cómo puedo detenerlo? Tenía 20k líneas que exporté a Excel, pero vlookup solo encontró alrededor de 9800 de ellas.

[Syndicate_Admin]

Por favor, pruebe esta versión. Debería ser significativamente más rápido.

OnDuration2 =
DÓNDE thisDT = 'Estado'[Marca de tiempo]
DÓNDE thisMachine = 'Estado'[MachineNumber]
DÓNDE thisMachinetable =
FILTRO ( 'Status', 'Status'[MachineNumber] = thisMachine )
DÓNDE nextoff =
BRIBONA (
FILTRO (
FILTRO ( thisMachinetable, 'Status'[Marca de tiempo] > thisDT ),
'Estado'[Estado] = "Desactivado"
),
'Estado'[Marca de tiempo]
)
DÓNDE prevon =
MAXX (
FILTRO (
FILTRO ( thisMachinetable, 'Status'[Marca de tiempo] < thisDT ),
'Estado'[Estado] = "Activado"
),
'Estado'[Marca de tiempo]
)
DÓNDE checkduplicateon =
ISBLANK (
BRIBONA (
FILTRO (
FILTRO (
thisMachinetable,
'Status'[Marca de tiempo] < thisDT
&& 'Estado'[Marca de tiempo] > prevon
),
'Estado'[Estado] = "Desactivado"
),
'Estado'[Marca de tiempo]
)
)
DÓNDE isfirstrow =
thisDT = BRIBONA ( thisMachinetable, 'Status'[Marca de tiempo] )
DEVOLUCIÓN
SI (
( isfirstrow || NO ( checkduplicateon ) )
&& 'Estado'[Estado] = "Activado",
DATEDIFF ( thisDT, nextoff, MINUTO )
)

Palmadita

[Syndicate_Admin]

Pensó en esto un poco más, y puede haber un enfoque aún más simple. Por favor, pruebe el último y éste e informe de nuevo. Este comprueba si la fila es una fila "On" y luego mide el tiempo hasta el siguiente estado para ese número de máquina (ya sea activado o desactivado). La suma de la nueva columna debe seguir siendo el mismo número de minutos totales.

OnDuration3 =
VAR thistime = 'Estado'[Marca de tiempo]
VAR thisMachine = 'Estado'[MachineNumber]
VAR la próxima vez =
MINX (
FILTRO (
«Estado»,
'Status'[Marca de tiempo] > esta vez
&& 'Status'[MachineNumber] = thisMachine
),
'Estado'[Marca de tiempo]
)
DEVOLUCIÓN
IF ( 'Status'[Status] = "On", DATEDIFF ( thistime, nexttime, MINUTE ) )

Palmadita

[Syndicate_Admin]

¡¡Hola!!
Tengo curiosidad... ¿Funcionó? Debido a que estoy probando las mismas soluciones y estoy teniendo el mismo problema con power bi, dice que se queda sin memoria ... ninguno de ellos está funcionando.

[Syndicate_Admin]

@ShakeAndBake ¿Alguna de estas expresiones modificadas mejoró las cosas?

Palmadita