ranking formula optimization

[Hemant_Jaiswar]

when i use this formula it goes on loading and takes lot of time to show output, how do i solve this problem so it loads faster

Ranking =

VAR _topCategory =

    RANKX(

        ALL(dwdim_customer[CustomerName]),  -- Remove context for CustomerName

        CALCULATE([CY #Value NOS], ALL(Dim_Date[Date])),  -- Ignore date context in measure calculation

        ,  -- No tie-breaking expression

        DESC  -- Rank in descending order for top N

    )

VAR _bottomCategory =

    RANKX(

        ALL(dwdim_customer[CustomerName]),  -- Remove context for CustomerName

        CALCULATE([CY #Value NOS], ALL(Dim_Date[Date])),  -- Ignore date context in measure calculation

        ,  -- No tie-breaking expression

        ASC  -- Rank in ascending order for bottom N

    )

VAR _ranking =

    IF(

        SELECTEDVALUE('Top OR Bottom'[Value]) = "Top",

        _topCategory,

        _bottomCategory

    )

VAR _TOPN =

    SELECTEDVALUE('Top N Parameter'[Top N Parameter], 5)  -- Use the parameter value (default to 5 if not selected)

RETURN

    IF(

        _ranking <= _TOPN,  -- Check if the rank is within the top N or bottom N range

        [CY #Value NOS] ,BLANK() -- Return the measure if within rank

    )

[Anonymous]

Hi,

Thanks for the solution grazitti_sapna , Kedar_Pande  and dharmendars007  offered, and i want to offer some more information for user to refer to.

hello @Hemant_Jaiswar , based on your descroption, you can try the following measure.

Ranking =
VAR _topCategory =
    RANKX (
        ALLSELECTED ( dwdim_customer[CustomerName] ),
        -- Remove context for CustomerName
        CALCULATE (
            [CY #Value NOS],
            removefilter ( Dim_Date[Date] )
        ),
        ,
        -- No tie-breaking expression
        DESC -- Rank in descending order for top N
    )
VAR _bottomCategory =
    RANKX (
        ALLSELECTED ( dwdim_customer[CustomerName] ),
        -- Remove context for CustomerName
        CALCULATE (
            [CY #Value NOS],
            removefilter ( Dim_Date[Date] )
        ),
        ,
        -- No tie-breaking expression
        ASC -- Rank in ascending order for bottom N
    )
VAR _ranking =
    IF (
        SELECTEDVALUE ( 'Top OR Bottom'[Value] ) = "Top",
        _topCategory,
        _bottomCategory
    )
VAR _TOPN =
    SELECTEDVALUE ( 'Top N Parameter'[Top N Parameter], 5 ) -- Use the parameter value (default to 5 if not selected)
RETURN
    IF (
        _ranking <= _TOPN,
        -- Check if the rank is within the top N or bottom N range
        [CY #Value NOS],
        BLANK () -- Return the measure if within rank
    )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Could you share some data to work with and show the expected result.  Let's see if there is some other way to solve the question.  Share a small sample dataset just so that i can focus on drafting a different solution.  You may then plug in your actual data to test the performance of the measure.

[grazitti_sapna]

Hi @Hemant_Jaiswar,

The provided ranking formula has performance issues because it calculates ranks across the entire dataset using the RANKX function, and it performs multiple context manipulations in both _topCategory and _bottomCategory. Optimizing this requires reducing the dataset size processed and avoiding unnecessary context modifications. Here's how you can improve the formula for better performance:

Optimized Formula

Ranking =
VAR _selectedRankingType = SELECTEDVALUE('Top OR Bottom'[Value], "Top")
VAR _topN = SELECTEDVALUE('Top N Parameter'[Top N Parameter], 5) -- Default to 5 if not selected

RETURN
IF (
_selectedRankingType = "Top",
CALCULATE(
[CY #Value NOS],
TOPN(_topN, ALL(dwdim_customer[CustomerName]), [CY #Value NOS], DESC)
),
CALCULATE(
[CY #Value NOS],
TOPN(_topN, ALL(dwdim_customer[CustomerName]), [CY #Value NOS], ASC)
)
)

Explanation of Changes

1. Replace RANKX with TOPN:
   •  TOPN is computationally more efficient than RANKX because it only returns the top or bottom N rows instead of ranking all rows.
   • By using TOPN, you directly filter the dataset to the desired number of top or bottom categories, reducing the load.
2.  Simplify Context Switching:
   •  ALL(dwdim_customer[CustomerName]) is still used, but only within the TOPN function, reducing unnecessary context evaluations.
3.  Avoid Calculating Both Rankings Simultaneously:
   •  The original formula computes both _topCategory and _bottomCategory regardless of which ranking type is selected. This formula calculates only the needed ranking based on the slicer selection (Top OR Bottom).
4. Default Parameter Values:
   •  Added a default value for Top N Parameter to prevent issues when no value is selected, ensuring robustness.
5. Remove Excessive BLANK Evaluations:
   • The BLANK() logic is integrated naturally with TOPN, avoiding additional calculations.

I hope the provided solution works for you

If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.

[Kedar_Pande]

@Hemant_Jaiswar 

To improve the performance of your DAX measure, avoid recalculating the same measure multiple times. Store the result of [CY #Value NOS] in a variable, and use ALLSELECTED instead of ALL for the RANKX function to respect any filters. This will reduce the data being processed and improve speed. Additionally, consider pre-aggregating your data or using summary tables to avoid heavy computations during runtime.

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[dharmendars007]

Hello @Hemant_Jaiswar , 

Instead of removing all context with ALL(dwdim_customer[CustomerName]), consider using ALLSELECTED(). This keeps the context of other filters, which can significantly reduce the number of calculations.

VAR _topCategory =
RANKX(
ALLSELECTED(dwdim_customer[CustomerName]),
CALCULATE([CY #Value NOS], ALL(Dim_Date[Date])),,DESC)

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN