Como mejorar esta formula (velocidad de carga)

[vicjordan]

Buenos dias, tengo esta formula que funciona perfectamente, pero tarda mucho en cargar cada que que aplico un filtro. Unos 20 segundos de media. Mi pregunta es si se puede optimizar de tal forma que no tarde tanto la carga, soy bastante nuevo y se que habría infinitos caminos pero no se me ocurre nada mas. Muchas gracias de antemano.

RANKING PUNTOS RAPIDA =

IF(ISFILTERED ('TABLA DE FECHAS'[Nº SEMANA Y MES])||ISFILTERED('TABLA DE FECHAS'[DIA MES]),

IF(

CALCULATE (

RANKX ( ALL ( 'AGENTES Y HORARIOS'[AGENTE] ), [PUNTOS],, DESC, SKIP ),

'INCENTIVOS SEMANALES'[TURNO ONL/RFZ/TLV] = "ONLINE"

)=1 && [VENTAS Solo BA] >= MAX ( 'INCENTIVOS SEMANALES'[BA.A NECESARIAS] )||

CALCULATE (

RANKX ( ALL ( 'AGENTES Y HORARIOS'[AGENTE] ), [PUNTOS],, DESC, SKIP ),

'INCENTIVOS SEMANALES'[TURNO ONL/RFZ/TLV] = "REFUERZO"

)=1 && [VENTAS Solo BA] >= MAX ( 'INCENTIVOS SEMANALES'[BA.A NECESARIAS] )||

CALCULATE (

RANKX ( ALL ( 'AGENTES Y HORARIOS'[AGENTE] ), [PUNTOS],, DESC, SKIP ),

'INCENTIVOS SEMANALES'[TURNO ONL/RFZ/TLV] = "TELEVENTA"

)=1 && [VENTAS Solo BA] >= MAX ( 'INCENTIVOS SEMANALES'[BA.A NECESARIAS] ),

" 💲 Cobra, tiene " & ROUND ( [PUNTOS], 1 ) & " puntos y superavit de " & [VENTAS Solo BA]-MAX('INCENTIVOS SEMANALES'[BA.A NECESARIAS]) & " ventas",

IF([VENTAS Solo BA]>=MAX('INCENTIVOS SEMANALES'[BA.A NECESARIAS])&& [VENTAS Solo BA]>0,

"❌No cobra, tiene " & ROUND ( [PUNTOS], 1 ) & " puntos y superavit de " & [VENTAS Solo BA]-MAX('INCENTIVOS SEMANALES'[BA.A NECESARIAS]) & " ventas",

"❌No cobra, tiene " & ROUND ( [PUNTOS], 1 ) & " puntos y le faltan " & ABS( MAX('INCENTIVOS SEMANALES'[BA.A NECESARIAS])-[VENTAS Solo BA]) & " ventas")),BLANK())

[Syndicate_Admin]

El uso de variables debería acelerarlo

RANKING POINTS FAST =
IF (
    ISFILTERED ( 'TABLE OF DATES'[Nº WEEK AND MONTH] )
        || ISFILTERED ( 'TABLE OF DATES'[DAY MONTH] ),
    VAR SalesOnlyBA = [SALES ONLY BA]
    VAR MaxBANecessary =
        MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
    RETURN
        IF (
            CALCULATE (
                RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
                'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "ONLINE"
            ) = 1
                && SalesOnlyBA >= MaxBANecessary
                || CALCULATE (
                    RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
                    'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "REINFORCEMENT"
                ) = 1
                    && SalesOnlyBA >= MaxBANecessary
                || CALCULATE (
                    RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
                    'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
                ) = 1
                    && SalesOnlyBA >= MaxBANecessary,
            " :heavy_dollar_sign: Cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of " & SalesOnlyBA - MaxBANecessary & "sales",
            IF (
                SalesOnlyBA >= MaxBANecessary
                    && SalesOnlyBA > 0,
                ":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of " & SalesOnlyBA - MaxBANecessary & "sales",
                ":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing "
                    & ABS ( MaxBANecessary - SalesOnlyBA ) & "sales"
            )
        ),
    BLANK ()
)

[vicjordan]

Esta formula ha mejorado solo un segundo la original pero algo es algo, se agradece el esfuerzo.

[Syndicate_Admin]

@Syndicate_Admin ,

Puedes probar el siguiente código :-

RANKING POINTS FAST =
IF (
    OR (
        ISFILTERED ( 'TABLE OF DATES'[Nº WEEK AND MONTH] ),
        ISFILTERED ( 'TABLE OF DATES'[DAY MONTH] )
    ),
    IF (
        CALCULATE (
            RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
            FILTER (
                'WEEKLY INCENTIVES',
                'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "ONLINE"
            )
        ) = 1
            && [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
            || CALCULATE (
                RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
                FILTER (
                    'WEEKLY INCENTIVES',
                    'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "REINFORCEMENT"
                )
            ) = 1
                && [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
            || CALCULATE (
                RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
                FILTER (
                    'WEEKLY INCENTIVES',
                    'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
                )
            ) = 1
                && [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
        " :heavy_dollar_sign: Cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
            & [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
        IF (
            [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
                && [SALES ONLY BA] > 0,
            ":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
                & [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
            ":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing "
                & ABS ( MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) - [SALES ONLY BA] ) & "sales"
        )
    ),
    BLANK ()
)

y también puede intentar mantener todo el código de cálculo en las variables separadas y usarlo en la condición Si.

Gracias

Samarth

[Syndicate_Admin · Accepted solution]

@Syndicate_Admin También puedes probar esto:

RANKING POINTS FAST =
VAR _caseOnline =
    CALCULATE (
        RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
        FILTER (
            'WEEKLY INCENTIVES',
            'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "ONLINE"
        )
    )
VAR _caseREINFORCEMENT =
    CALCULATE (
        RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
        FILTER (
            'WEEKLY INCENTIVES',
            'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "REINFORCEMENT"
        )
    )
VAR _caseTELESHOPPING =
    CALCULATE (
        RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
        FILTER (
            'WEEKLY INCENTIVES',
            'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
        )
    )
RETURN
    IF (
        OR (
            ISFILTERED ( 'TABLE OF DATES'[Nº WEEK AND MONTH] ),
            ISFILTERED ( 'TABLE OF DATES'[DAY MONTH] )
        ),
        IF (
            AND (
                OR (
                    _caseReinforcement = 1
                        && [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
                    OR (
                        _caseREINFORCEMENT = 1
                            && [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
                        _caseTeleshopping = 1
                    )
                ),
                [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
            ),
            " :heavy_dollar_sign: Cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
                & [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
            IF (
                AND (
                    [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
                    [SALES ONLY BA] > 0
                ),
                ":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
                    & [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
                ":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing "
                    & ABS ( MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) - [SALES ONLY BA] ) & "sales"
            )
        ),
        BLANK ()
    )

[vicjordan]

gran trabajo¡ De esta manera ha bajado significativamente el tiempo de carga.

Muchas gracias 

[Syndicate_Admin]

@vicjordan

Por favor, pruebe esta fórmula. Puede ser más rápido.

RANKING POINTS FAST =
IF (
    ISFILTERED ( 'TABLE OF DATES'[Nº WEEK AND MONTH] )
        || ISFILTERED ( 'TABLE OF DATES'[DAY MONTH] ),
    VAR SalesOnlyBA = [SALES ONLY BA]
    VAR MaxBANecessary =
        MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
    RETURN
        IF (
            SalesOnlyBA >= MaxBANecessary,
            IF (
                CALCULATE (
                    RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
                    'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "ONLINE"
                ) = 1
                    || CALCULATE (
                        RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
                        'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "REINFORCEMENT"
                    ) = 1
                    || CALCULATE (
                        RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
                        'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
                    ) = 1,
                " :heavy_dollar_sign: Cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of " & SalesOnlyBA - MaxBANecessary & "sales",
                IF (
                    SalesOnlyBA > 0,
                    ":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of " & SalesOnlyBA - MaxBANecessary & "sales",
                    ":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing "
                        & ABS ( MaxBANecessary - SalesOnlyBA ) & "sales"
                )
            )
        )
)

Saludos
Equipo de soporte de la comunidad _ Jing
Si esta publicación ayuda, por favor acéptala como Solución para ayudar a otros miembros a encontrarla.