Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
newgirl
Helper V
Helper V

DAX complicado usando SUMX

¡Hola! Espero que alguien pueda ayudarme con mi problema.

Tengo este archivo que estoy tratando de averiguar por qué los números están cambiando con sólo actualizar los datos. Lamentablemente, este archivo acaba de ser transmitido por un desarrollador de Power BI que nuestra empresa contrató, pero su contrato no se renovó. Así que querían que nuestro departamento continuara lo que el desarrollador ya ha construido, pero como el desarrollador ya no trabaja con nuestra empresa, no podemos hacerle preguntas sobre el proyecto. Además, no estuve involucrado en las reuniones cuando ocurrió el volumen de negocios.

Así que este proyecto informa de los KPI de nuestras entregas de productos y fui nombrado solo para KPI específicos. Uno de los KPI es calcular el número de horas que un camión ha trabajado. Lo llamamos "Horas trabajadas completadas" y el desarrollador colocó en el archivo una matriz de las horas trabajadas completadas por número de placa.

Correct DataDatos correctos

La matriz también se filtra en la que los viajes de destino son mayores que Cero. A continuación se muestra el código DAX para la medida Viajes de destino.

Target Trips =
SUMX (
    VALUES ( CutOff[Date] ),
    SUMX (
        FILTER (
            fTCFleetNeg,
            CutOff[Date] >= fTCFleetNeg[From]
                && CutOff[Date] <= fTCFleetNeg[To]
        ),
        fTCFleetNeg[Trip_Incentives] / ( fTCFleetNeg[To] - fTCFleetNeg[From] + 1 )
    )
)

Ilustración de la Datos

A continuación se muestra un ejemplo de datos de una de las tablas (fTMS) que enumera los detalles de cada viaje. El KPI calcularía cuántas horas ha trabajado el camión.

DTSStagetime_inlinetime_backinplantdate_inlinedate_backinplantRealPlate
Completado12:45:00 PM1:30:00 AMmartes, 3 de marzo de 2020Jueves, 5 de marzo de 2020ABC335
Completado2:17:00 PM12:45:00 PMSábado, 29 de febrero de 2020martes, 3 de marzo de 2020ABC335
Completado1:30:00 AM2:00:00 AMJueves, 5 de marzo de 2020viernes, 6 de marzo de 2020ABC335
Completado6:51:00 AM3:41:00 AMDomingo, 23 de febrero de 2020Viernes, 28 de febrero de 2020ABC335

Dados estos datos, si lo calcule manualmente, el camión trabajó durante 248,55 horas. Sin embargo, hay una segmentación de datos en la página que filtra el date_backinplant del 28 de febrero al 29 de marzo solamente. Lo que significa que dentro de ese período, el camión ha trabajado sólo durante 135.40 horas (248.55 menos 17.15 y 96 porque ya no incluimos las horas del 23 de febrero al 27 de febrero).

DTSStagetime_inlinetime_backinplantdate_inlinedate_backinplantRealPlateHorario en líneaIn BetweenBackinPlant Horas
Completado12:45:00 PM1:30:00 AMmartes, 3 de marzo de 2020Jueves, 5 de marzo de 2020ABC33511.2524.001.50
Completado2:17:00 PM12:45:00 PMSábado, 29 de febrero de 2020martes, 3 de marzo de 2020ABC3359.7248.0012.75
Completado1:30:00 AM2:00:00 AMJueves, 5 de marzo de 2020viernes, 6 de marzo de 2020ABC33522.50 2.00
Completado6:51:00 AM3:41:00 AMDomingo, 23 de febrero de 2020Viernes, 28 de febrero de 2020ABC33517.1596.003.68

De todos modos, la fórmula DAX que el desarrollador creó para calcular para esto es:

Hours Worked Completed =
CALCULATE (
    CALCULATE (
        SUMX (
            VALUES ( 'Calendar'[Date] ),
            SUMX (
                FILTER (
                    fTMS,
                    fTMS[DTSStage] = "COMPLETED"
                        && NOT ( ISBLANK ( fTMS[date_inline] ) )
                        && fTMS[date_inline] < 'Calendar'[Date]
                        && fTMS[date_backinplant] > 'Calendar'[Date]
                ),
                24
            )
        ),
        CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )
    )
        + CALCULATE (
            SUMX (
                VALUES ( 'Calendar'[Date] ),
                SUMX (
                    FILTER (
                        fTMS,
                        fTMS[DTSStage] = "COMPLETED"
                            && NOT ( ISBLANK ( fTMS[date_inline] ) )
                            && fTMS[date_inline] = 'Calendar'[Date]
                            && fTMS[date_backinplant] <> 'Calendar'[Date]
                    ),
                    24 * ( 1 - fTMS[time_inline] )
                )
            ),
            CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )
        )
        + CALCULATE (
            SUMX (
                VALUES ( 'Calendar'[Date] ),
                SUMX (
                    FILTER (
                        fTMS,
                        fTMS[DTSStage] = "COMPLETED"
                            && NOT ( ISBLANK ( fTMS[date_inline] ) )
                            && fTMS[date_inline] <> 'Calendar'[Date]
                            && fTMS[date_backinplant] = 'Calendar'[Date]
                    ),
                    24 * ( fTMS[time_backinplant] )
                )
            ),
            CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )
        )
        + CALCULATE (
            SUMX (
                VALUES ( 'Calendar'[Date] ),
                SUMX (
                    FILTER (
                        fTMS,
                        fTMS[DTSStage] = "COMPLETED"
                            && NOT ( ISBLANK ( fTMS[date_inline] ) )
                            && fTMS[date_inline] = 'Calendar'[Date]
                            && fTMS[date_backinplant] = 'Calendar'[Date]
                    ),
                    24 * ( fTMS[time_backinplant] - fTMS[time_inline] )
                )
            ),
            CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )
        ),
    CROSSFILTER ( CutOff[Date], 'Calendar'[Date], BOTH )
)

En realidad, aquí está la vista de modelo en la que resalto en verde las tablas implicadas y los campos con relación entre sí. Acabo de bloquear las otras tablas que no son utilizadas por las medidas involucradas.

Model ViewVista del modelo

Datos de ejemplo de otras tablas implicadas:

Tabla ftcFleetNeg. - es datos rellenados mensualmente, pero las fechas son sólo "Desde" y "Para"

DeParaP_NO
7/1/20197/31/2019Placa A
6/1/20196/30/2019Placa A
3/1/20193/31/2019Placa A
5/1/20195/31/2019Placa A
4/1/20194/30/2019Placa A
8/1/20198/31/2019ABC335
7/1/20197/31/2019ABC335
6/1/20196/30/2019ABC335
9/29/201910/29/2019ABC335
9/1/20199/28/2019ABC335
10/30/201911/28/2019ABC335
11/29/201912/27/2019ABC335
1/30/20202/27/2020ABC335
12/28/20191/27/2020ABC335
2/28/20203/29/2020

ABC335

Tabla RealPlateList - es sólo una tabla creada en la que "RealPlate_List - DISTINCT(fTMS[RealPlate])"

Calendario de tabla - tabla de calendario básica

Corte de tabla - es como una tabla de calendario, pero tiene una columna que clasifica qué fechas caerían en su "período de corte". Por ejemplo, etiquetaron las fechas de "marzo" del 28 de febrero al 29 de marzo.

Así que lo que es raro es que usando el archivo original que el desarrollador nos pasó que todavía no hice cambios, después de actualizarlo, los números mostrados en la matriz cambiarían, como el de abajo. A continuación se muestran muestras mezcladas con datos correctos (Placa A) y otros tres números de placa con números modificados (Placa B, C y ABC335). En general, la mayoría de las horas de los números de placa no cambiaron en realidad, excepto en la placa B, la placa C y la ABC335.

wrong matrix except for Plate A datamatriz incorrecta, excepto por los datos de la Placa A

Si lo comparo con el visual de matriz correcto, parece que hay 24 horas que se añaden en otros días a pesar de que el camión no viajó en ese día. Por ejemplo, para ABC335 en marzo, solo tiene el 3 de marzo, el 5 de marzo y el 6 de marzo en date_backinplant de campo. Así que no sé por qué está "llenando" 24 horas fuera de esas fechas.

Parece que esta es la razón por la que las horas aumentaron después de refrescar pero no pude encontrar el factor en lo que está causando este "llenado" de 24 horas dado que ya he comparado las tablas de antes de actualizar y después de actualizar, y los únicos cambios son los nuevos datos después de marzo.

¿Tendrías una idea de qué factor está causando esto? ¿Qué más puedo comprobar? Espero que alguien sea capaz de entender. Ya estoy al final del ingenio.

9 REPLIES 9
sreenathv
Solution Sage
Solution Sage

En lugar de la medida en su publicación, ¿puede probar la siguiente medida simplificada?

HoursWorkedCompleted =
VAR DurationDT =
    SUMX (
        fTMS,
        ( fTMS[date_backinplant] + fTMS[time_backinplant] ) - ( fTMS[date_inline] + fTMS[time_inline] )
    )
RETURN
    DurationDT * 24


Probé su fórmula sugerida con algunos filtros que agregué:

HoursWorkedCompleted = 
VAR DurationDT =
    SUMX (
        FILTER(fTMS, fTMS[DTSStage]= "COMPLETED" && NOT (ISBLANK(fTMS[date_inline]))),
        ( fTMS[date_backinplant] + fTMS[time_backinplant] ) - ( fTMS[date_inline] + fTMS[time_inline] )
    )
RETURN
    DurationDT * 24

Se calcula correctamente por fila. Sin embargo, la página tiene una segmentación de datos en la que permitiría al usuario ver cuántas horas se completaron en función del período dado. Así que si pruefio su medida y muestro las horas por día, se mostraría esto:

no 1.PNG

Al mirar la placa ABC335 bajo el 28 de febrero, técnicamente es correcto porque el total de horas trabajadas para ese viaje es de 116,83 horas, pero el viaje comenzó a partir del 23 de febrero y terminó el 28 de febrero. Por lo tanto, el número esperado en el 28 de febrero debe ser de 3,68 horas.

Supongo que es por eso que el desarrollador tuvo que crear una fórmula DAX larga. El problema es que no estoy acostumbrado a sumx, especialmente si es un sumx dentro de un sumx, así que no puedo rastrear lo que está causando las 24 horas adicionales en la matriz después de actualizar.

time_inlinetime_backinplantdate_inlinedate_backinplantRealPlate . . . Total
12:45:00 PM1:30:00 AMmartes, 3 de marzo de 2020Jueves, 5 de marzo de 2020PUX33511.2524.001.5036.75
2:17:00 PM12:45:00 PMSábado, 29 de febrero de 2020martes, 3 de marzo de 2020PUX3359.7248.0012.7570.47
1:30:00 AM2:00:00 AMJueves, 5 de marzo de 2020viernes, 6 de marzo de 2020PUX33522.502.00 24.50
6:51:00 AM3:41:00 AMDomingo, 23 de febrero de 2020Viernes, 28 de febrero de 2020PUX33517.1596.003.68116.83

.

Prueba esta Medida..

HoursWorkedCompleted =
VAR CurrentPeriod =
    CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), ALLSELECTED () )
VAR DurationDT =
    SUMX (
        FILTER (
            fTMS,
            fTMS[DTSStage] = "COMPLETED"
                && NOT ( ISBLANK ( fTMS[date_inline] ) )
        ),
        IF (
            fTMS[date_inline] IN CurrentPeriod,
            ( fTMS[date_backinplant] + fTMS[time_backinplant] ) - ( fTMS[date_inline] + fTMS[time_inline] ),
            MOD ( fTMS[time_backinplant], 1 )
        )
    )
RETURN
    DurationDT * 24

Hola, @sreenathv !

¡Muchas gracias por tomarse el tiempo para explicar la fórmula! Definitivamente seguiré volviendo a su explicación como referencia cuando me pierda en "visualizar" la estructura de la fórmula. Tampoco estoy seguro de si lo has notado, pero incluí en una de mis respuestas el enlace a la muestra pbix?

De todos modos, en cuanto a la segunda medida que sugeriste,

HoursWorkedCompleted =
VAR CurrentPeriod =
    CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), ALLSELECTED () )
VAR DurationDT =
    SUMX (
        FILTER (
            fTMS,
            fTMS[DTSStage] = "COMPLETED"
                && NOT ( ISBLANK ( fTMS[date_inline] ) )
        ),
        IF (
            fTMS[date_inline] IN CurrentPeriod,
            ( fTMS[date_backinplant] + fTMS[time_backinplant] ) - ( fTMS[date_inline] + fTMS[time_inline] ),
            MOD ( fTMS[time_backinplant], 1 )
        )
    )
RETURN
    DurationDT * 24

Agregué esta medida en el archivo original (sin actualizar) y aquí hay una comparación entre las 2 tablas (su medida está en la 2a tabla). El total general por placa es correcto, pero las horas por día son incorrectas.

0716 comparison.png

Tomando ABC335 como ejemplo y mirando los datos con computación manual, parece que su fórmula está agregando por viaje y mostrándolos el día de la fecha en Back In Plant. Al igual que 70.47 horas se graba el 3 de marzo en la 2a tabla cuando, de hecho, para el 3 de marzo, el camión trabajó durante 24 horas (11.25 + 12.75), como se muestra en la 1a tabla.

0716 manual.PNG

Sólo para reiterar mi problema, cuando descargo el archivo original que nos dejó y sin editar nada, no parece haber ningún problema con los números. Pero después de que me refresco, pero todavía sin mover ningún filtro o editar nada, los números de sólo 3 números de placa cambiaron, en el que parece que 24 horas adicionales estaban siendo "añadidos" en el resto de los días a pesar de que no tenían ningún viaje en ese día. Aparte de estos 3 números de placa, los otros cientos de números de placas están mostrando el no correcto. de horas trabajadas por día.

Para intentar rastrear el factor de causar los cambios, intenté descargar las tablas conectadas a las medidas desde el PBIX antes de actualizar y después de actualizar para compararlas entre sí, pero no parece tener ninguna diferencia.

Quería tener otra perspectiva sobre cómo abordar en el seguimiento del problema, pero ya he probado y probado varias cosas, pero todavía no puedo encontrarlo. 😞

En realidad, deberías ignorar todos mis mensajes. La fórmula escrita por el desarrollador es buena. No hay ningún error en él.

Antes, pasé por alto un punto. Está en la siguiente línea de código.

CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )

La explicación es que hay una relación entre el campo "Calendario[Fecha]" y el campo "ftms[date_backinplant]". Por lo tanto, al trazar la medida en una matriz con "Calendar[Date]" en columnas y "fmts[RealPlate]" en las filas, estos dos campos filtran la tabla fTMS y, como resultado, solo los registros con "date_backinplant" y "realplate" tienen registros en una fecha determinada se pasan al cálculo. Pero necesitas las "24 Horas" para todas las fechas entre "date_inline" y "date_backinplant". Para ello, el filtro del campo "Calendario[Fecha]" en "ftms[date_backinplant]" tuvo que ser eliminado. Se hizo usando la función CROSSFILTER() con "NONE" como el 3er paramenter que elimina el filtro de "Calendar[Date]" en la tabla fTMS.

Todo es perfecto hasta ahora. Pero el problema surgió cuando tenía el siguiente registro en sus datos.

DTSStagetime_inlinetime_backinplantdate_inlinedate_backinplantRealPlate
Completado02:00:0021:24:0006-03-202008-06-2020ABC335

En este registro, el date_backinplant tiene un valor del 6 de junio, pero date_inline tiene un valor de 6o de marzo, y debido a la CROSSFILTER con "NONE" como parámetro, este registro no se filtró aunque la fecha del 6o jun de date_backinplant queda fuera de su selección de fecha. Por lo tanto, a partir del 6 de marzo de 2020, para cada día, la fórmula insertó 24 horas hasta el último día en su matriz, que es el 29 de marzo de 2020.

Si desea omitir los registros con "date_backinplant" que quedan fuera del rango de fechas seleccionado, tendrá que agregar exclusivamente un filtro a su tabla fTMS en el código en algún lugar cerca del filtro en la etapa "COMPLETED".

Hola !!! @sreenathv ¡Muchas gracias por su respuesta! Tenías razón, es ese tipo de transacción que estaba afectando a las "adicionales" 24 horas cuando se actualizaron los datos. No pude localizar esta fuente de discrepancia ya que estaba limitando mi comprobación de datos dentro de las fechas de marzo en la parte posterior de la planta. Una vez más, ¡muchas gracias! Estoy muy agradecida. Usted fue muy servicial y sus respuestas fueron muy informativas!

sreenathv
Solution Sage
Solution Sage

Me gustaría recrear su escenario en mi PBI Desktop. ¿Podrá enviar algunos datos de muestra. He intentado usar los datos de muestra que ha publicado. Parece que es inadecuado.

Tengo las siguientes tablas de datos de ejemplo de su publicación.

1) Calendario

2) ftcFleetNeg

3) fTMS

4) RealPlateList

Si cree que estos datos son suficientes para recrear el escenario, envíe algunas entradas relevantes para la siguiente tabla también

"Table Cutoff - es como una tabla De calendario, pero tiene una columna que clasifica qué fechas caerían en su "período de corte". Por ejemplo, etiquetaron las fechas de "marzo" del 28 de febrero al 29 de marzo".

La tabla CutOff también se utiliza en el código DAX. Así que también necesitaré algunos datos relevantes para esta tabla.

Además, usted dijo que hay un filtro de fecha en el informe. ¿Se filtra en función de "Calendar[Date]" o fTMS[date_backinplant] ?

Será útil si puede crear un archivo pbix con todos los datos de muestra y códigos DAX para que podamos solucionarlo fácilmente.

Hola, @sreenathv ! He copiado sólo las columnas relevantes de la base de datos y cargado en el archivo pbix. También ya puse en las medidas.

Otra cosa extraña que encuentro es que incluso si trato de replicar esta parte del informe como esta, todavía no obtengo los números correctos a pesar de que sólo copio las medidas y los datos. Pero de todos modos, la salida en la muestra pbix tiene la misma "enfermedad" en la que hay 24 horas que se muestran en los días que el camión no tenía viajes.

https://drive.google.com/file/d/1xpJyjpu54H0Penra0PJ124MsguGtdXMl/view?usp=sharing

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors