Forum Discussion

Hemant_Jaiswar's avatar
Hemant_Jaiswar
Helper I
1 year ago

ranking formula optimization

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_cu...
Need Help
Show and Tell
Tips and Tricks
grazitti_sapna's avatar
grazitti_sapna
Super User
1 year ago

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.