Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

How to improve this formula (loading speed)

Good morning, I have this formula that works perfectly, but it takes a long time to load every time I apply a filter. About 20 seconds on average. My question is if it can be optimized in such a way that it does not take so long to load, I am quite new and I know that there would be infinite paths but I can not think of anything else. Thank you very much in advance.

RANKING POINTS FAST =


IF(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 ),
'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 ),
'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 ),
'WEEKLY INCENTIVES' [SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
)=1 && [SALES ONLY BA] >= Max ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
" 💲 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,
"❌No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of " & [SALES ONLY BA]-Max('WEEKLY INCENTIVES'[BA. A NECESSARY]) & "sales",
"❌No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing " & Abs( Max('WEEKLY INCENTIVES'[BA. A NECESSARY])-[SALES ONLY BA]) & "sales")),BLANK())

6 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    Hi Syndicate_Admin ,

     

    You could try below code :-

    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 ()
)

    and you can also try to keep your all calculate code in the seperate variables and use it in If condition.

     

    Thanks,

    Samarth

    • Samarth_18's avatar
      Samarth_18
      Community Champion
      3 years ago

      Syndicate_Admin You can also try this:-

      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 ()
    )
      • Syndicate_Admin's avatar
        Syndicate_Admin
        Administrator
        3 years ago

        In this way the loading time has been significantly lowered.

        Thanks a lot

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Using variables should speed it up

    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 ()
)
    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      This formula has improved only one second the original but something is something, the effort is appreciated.