RANKX working too slow

[RavitPBI]

I have 6 measures based on which I am generating a ranked scoring (meaning, based on the value of the measure a rank is assigned which acts as its score, to get the overall score/rank). Measures are as follows:

Measure1 (Units Handled): [UH] = DIVIDE(SUM(Fact_Table1[Units]), SUM(Fact_Table1[Hours]))

Measure2 (Cost per Unit): [CPU] = DIVIDE(SUM(Fact_Table1[Cost]), SUM(Fact_Table1[Units]))

Measure3 (Budgeted Units Handled): [B_UH] = DIVIDE(SUM(Fact_Table2[Budgeted_Units]), SUM(Fact_Table2[Budgeted_Hours]))

Measure4 (Budgeted Cost per Unit): [B_CPU] = DIVIDE(SUM(Fact_Table2[Budgeted_Cost]), SUM(Fact_Table2[Budgeted_Units]))

Measure5: [Delta_UH] = [UH] - [B_UH]

Measure6: [Delta_CPU] = [CPU] - [B_CPU]

[UH_Score] = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]),[UH],, ASC) # Higher UH = Higher Score/Rank

[CPU_Score] = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]), [CPU],,DESC) # Lower CPU = Higher Score/Rank

[Delta_UH_Score] = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]),[Delta_UH],, ASC) # Higher Delta_UH = Higher Score/Rank

[Delta_CPU_Score] = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]),[Delta_CPU],, DESC) # Lower Delta_CPU = Higher Score/Rank

[Overall_Score] = ([UH_Score] + [CPU_Score] + [Delta_UH_Score] + [Delta_CPU_Score]) / 4

[Overall_Rank] = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]), [Overall_Score])

Basically, I am looking to Rank all my Organizations based on the scoring from the aforementioned 6 measures. But this final ranking is coming out to be TOO SLOW and I am sure there could be a better and optimized way to solve this issue - either through better DAX, or additional measure(s), or another creative workaround. 

I'd really appreciate any help with this issue.

[v-yangliu-msft]

Hi  @RavitPBI  ,

You can simplify these measures by putting them into a measure by definition

Measure =
Var _UH_Score = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]),[UH],, ASC) 
Var _CPU_Score = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]), [CPU],,DESC) 
Var _Delta_UH_Score = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]),[Delta_UH],, ASC) 
Var _Delta_CPU_Score = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]),[Delta_CPU],, DESC)
Var _Overall_Score= (_[UH_Score + _CPU_Score +  _Delta_UH_Score + _Delta_CPU_Score) / 4
Var _Overall_Rank = RANKX(ALLSELECTED('Dim_Organization'[Organization_cd]), __Overall_Score)
return _Overall_Rank

Here is a link of related contents, hoping to help you:

https://www.sqlbi.com/articles/how-to-compute-index-numbers-at-top-speed/

Best Regards,

Liu Yang

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