Mejorar el rendimiento - Medir DAX

[Anonymous]

Hola

Opté por publicar este problema aquí tiene que se puede ver como uno separado. El problema original está aquí. (Cálculo FTE teniendo en cuenta los días laborables de cada actividad y los días laborables del contexto seleccionado)

Se me ocurrió una medida para el cálculo de FTE, pero, como mi tabla FACTS tiene 3.610.402 filas, supongo que esa es la razón por la que está tardando demasiado desde una perspectiva de rendimiento. Aparentemente está funcionando para 1 o 2 actividades. Pero si pongo es analizar todo el conjunto de datos, se vuelve demasiado lento.

¿Sabes alguna forma de mejorar el rendimiento de esta medida?

Total FTEs = 
VAR _FirstDate =
    FIRSTDATE ( 'DateC'[Date] )
VAR _LastDate =
    LASTDATE ( 'DateC'[Date] )

RETURN
    SUMX(FACTS, CALCULATE (
        SUM ( FACTS[Result gross (FTE)] )
            * DIVIDE (
                CALCULATE (
                    SUM ( 'DateC'[WorkingDay] ),
                    DATESBETWEEN ( 'DateC'[Date], MAx(_FirstDate,SUM(FACTS[Start date])), MIN(_LastDate,SUM(FACTS[Finish date]) ))
                ),
                Calculate(
                    SUM ( 'DateC'[WorkingDay] ),
                    DATESBETWEEN ( 'DateC'[Date], _FirstDate, _LastDate )
                ),
                0
            ),
        FACTS[Start date] <= _LastDate,
        FACTS[Finish date] >= _FirstDate
    ))

Lo que se esperaría, sería algo como esto, que funciona para un pequeño número de actividades, pero no para todo el conjunto de datos (tenía el horizonte temporal de 2020 a 2024 seleccionado):

• Medir los FTEs totales:en el cuadro, como el contexto es 2020-2024, calcula de acuerdo con la duración de 5 años;
• En el gráfico, realiza el cálculo de acuerdo con el período mostrado, lo que significa que:
  • Junio 2020: 0.0716 FTEs;
  • Julio 2020: 0.0727 FTEs - tiene en cuenta los días laborables de una de las actividades, aumentando
  • Los otros meses siguen una lógica similar.
• Lo que también sería un buen tener, pero no debe tener, es tener todos los meses mostrando en el eje X. He seleccionado 2020-2024 como filtro de página, pero el gráfico muestra sólo hasta enero de 2024 - la fecha de finalización de la primera actividad

¡Muchas gracias!

Pedro

[AntrikshSharma]

@PJogo por supuesto que será un código lento, está haciendo LA TRANSICION DE CONTEXTO 3,6 millones de veces sobre una tabla de hechos. No hay mucho o espacio para la optimización, todo tiene que ser escrito desde cero, se puede proporcionar archivo PBI? de lo contrario, ¿tiene una columna de año mes en su tabla de fechas? si no crea uno e intenta usar esto:

Total FTEs =
VAR _FirstDate =
    MIN ( 'DateC'[Date] )
VAR _LastDate =
    MAX ( 'DateC'[Date] )
RETURN
    SUMX (
        VALUES ( Dates[Year Month] ),
        CALCULATE (
            SUM ( FACTS[Result gross (FTE)] )
                * DIVIDE (
                    CALCULATE (
                        SUM ( 'DateC'[WorkingDay] ),
                        DATESBETWEEN (
                            'DateC'[Date],
                            MAX ( _FirstDate, SUM ( FACTS[Start date] ) ),
                            MIN ( _LastDate, SUM ( FACTS[Finish date] ) )
                        )
                    ),
                    CALCULATE (
                        SUM ( 'DateC'[WorkingDay] ),
                        DATESBETWEEN ( 'DateC'[Date], _FirstDate, _LastDate )
                    ),
                    0
                ),
            FACTS[Start date] <= _LastDate,
            FACTS[Finish date] >= _FirstDate
        )
    )

Además, no entiendo por qué sumas fechas. Esta parte MAX ( _FirstDate, SUM ( FACTS[Start date] ) ) ) en el código no tiene mucho sentido. Trate de depurar y ver lo que devuelve y si no es útil a continuación, eliminarlo, porque es una parte de DATESBETWEEN, estoy seguro de que las fechas de suma no es necesario.

[daxer-almighty · Accepted solution]

@PJogo ,

Aquí hay una versión que no utiliza CALCULATE y debe ser MUCHO más rápido. Echa un buen vistazo a su mecánica y si no funciona en el primer intento, no te asustes, sólo trata de entender cómo funciona y ajustarlo en consecuencia. Pero tenga cuidado con CALCULATE que se ejecuta fila por fila en una tabla de hechos. NUNCA DEBES HACERLO, YA QUE SIEMPRE MATARÁ EL RENDIMIENTO.

Total FTEs =
// Dates must NOT be connected to Facts
// and must be marked as a Date table
VAR __firstDate = MIN( 'Dates'[Date] )
VAR _lastDate = MAX( 'Dates'[Date] )
// assuming that WorkingDay is 1 for
// a working day and 0 for a weekend
var __workingDayCount = SUM( 'Dates'[WorkingDay] )
RETURN
if( __workingDayCount > 0,
    SUMX(
    
        FILTER(
            FACTS,
            // getting only the rows where
            // there is a non-empty overlap
            // between (start, end) and
            // (firstDate, lastDate)
            Facts[Start date] <= __lastDate
            &&
            Facts[Finish date] >= __firstDate
        ),
        
        // for each of the above rows calculate
        // the percentage of FTE
        var __fte = FACTS[Result gross (FTE)]
        var __lowerDate =
            MAX(
                __firstDate,
                Facts[Start date]
            )
        var __upperDate =
            MIN(
                __lastDate,
                Facts[Finish date]
            )            
        var __activityWorkingDayCount =
            SUMX(
                filter(
                    // We don't have to use
                    // ALL( Dates ) here due
                    // to the nature of the
                    // problem.
                    Dates,
                    and(
                        __lowerDate <= Dates[Date],
                        Dates[Date] <= __upperDate
                    )
                ),
                 Dates[WorkingDay]
            )
        return
            // do not use DIVIDE here as it
            // does nothing more than what
            // I've put in here and in fact
            // it slows down calculations
            __fte * __activityWorkingDayCount
                / __workingDayCount
    )
)

[daxer-almighty]

A propósito... @PJogo,estás usando y abusando de CALCULATE. Por favor, lea al menos algunos capítulos del libro definitivo sobre DAX "The Definitive Guide to DAX" de Alberto Ferrari y Marco Russo. Me lo agradecerás más tarde 🙂

[Anonymous]

Hola @AntrikshSharma ,

En primer lugar, gracias por su ayuda, muy apreciado!

Traté de usar su enfoque e hice lo siguiente:

• Se ha creado una columna YearMonth (AAAA) en la tabla DateC
• Se ha creado una versión reanudada de la tabla FACTS

Los resultados no son adecuadamente lo que esperaba. Y los valores calculados tampoco coinciden con los valores anteriores de cada fila de actividad.

El código era este:

Total FTEs PBI Com = 
VAR _FirstDate =
    MIN ( 'DateC'[Date] )
VAR _LastDate =
    MAX ( 'DateC'[Date] )
RETURN
    SUMX (
        VALUES ( DateC[YearMonth] ),
        CALCULATE (
            SUM ( FACTSShort[Result gross (FTE)] )
                * DIVIDE (
                    CALCULATE (
                        SUM ( 'DateC'[WorkingDay] ),
                        DATESBETWEEN (
                            'DateC'[Date],
                            MAX ( _FirstDate, SUM ( FACTSShort[Start date] ) ),
                            MIN ( _LastDate, SUM ( FACTSShort[Finish date] ) )
                        )
                    ),
                    CALCULATE (
                        SUM ( 'DateC'[WorkingDay] ),
                        DATESBETWEEN ( 'DateC'[Date], _FirstDate, _LastDate )
                    ),
                    0
                ),
            FACTSShort[Start date] <= _LastDate,
            FACTSShort[Finish date] >= _FirstDate
        )
    )

Además, intentó quitar la SUMA de la fórmula BetweenDates, pero se mostró un error: "No se puede determinar un único valor para la columna 'Fecha de inicio' en la tabla 'FACTSShort'. Esto puede suceder cuando una fórmula de medida hace referencia a una columna que contiene muchos valores sin especificar una agregación como min, max, count o sum para obtener un único resultado."

Voy a tratar de pensar en otra manera para abordar esto...

¡Gracias!