ranking formula optimization

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 

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.

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.

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.

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.