Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Syndicate_Admin
Administrator
Administrator

How to improve this formula (loading speed)

‎09-27-2022 03:21 AM

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())
Message 1 of 7
329 Views
0
6 REPLIES 6
johnt75
Super User
Super User

‎09-27-2022 03:45 AM

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 ()
)
Message 6 of 7
287 Views
0
Syndicate_Admin
Administrator
Administrator
In response to johnt75

‎09-27-2022 04:17 AM

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

Message 7 of 7
279 Views
0
Samarth_18
Community Champion
Community Champion

‎09-27-2022 03:35 AM

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Message 2 of 7
290 Views
0
Samarth_18
Community Champion
Community Champion
In response to Samarth_18

‎09-27-2022 04:30 AM

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Message 3 of 7
275 Views
0
Syndicate_Admin
Administrator
Administrator
In response to Samarth_18

‎09-27-2022 05:23 AM

In this way the loading time has been significantly lowered.

Thanks a lot

Message 4 of 7
268 Views
0
v-jingzhang
Community Support
Community Support
In response to Syndicate_Admin

‎09-28-2022 03:03 AM

Hi @vicjordan 

 

Please try this formula. It may be faster. 

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

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Message 5 of 7
260 Views
0

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

View All