Forum Discussion

a4's avatar
a4
Helper III
6 years ago

How to optimize the RANKX measure

Hi all

 

I have a  rank measure:

 

Rank  = CALCULATE( RANKX(ALL('salesorderdetails'[Trim name]),ROUND([Gross Premium(USD) minus Commission (USD)],2),,DESC,Dense),ALLSELECTED('salesorderdetails'),VALUES('salesorderdetails'[Trim name]))
When I remove this measure from table visual it brings down the excution time down to 2 seconds and performance is improved. So, clearly there is a problem with the Rank measure.
 
Please suggest a way to optimize the RANKX function.
 
Kind Regards
Amit Kumar
Need Help

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi a4 ,

     

    Try this.

    Rank =

    RANKX(ALL('salesorderdetails'[Trim name]),ROUND([Gross Premium(USD) minus Commission (USD)],2),,DESC,Dense)

     

    Also, if you can share sample data. 

     

    Regards,

    Harsh Nathani

     

     

     

    • a4's avatar
      a4
      Helper III
      6 years ago

      Hi Harsh,

      The formula which you mentioned doesn't give the desired result.All the Trim Name has rank with this formula.

      Please suggest anything else.

       

       

      Kind Regards

      Amit Kumar

      • a4's avatar
        a4
        Helper III
        6 years ago

        Hi Harsh,

        The formula which you mentioned doesn't give the desired result.All the Trim Name has the same rank with this formula.

        Please suggest anything else.

         

         

        Kind Regards

        Amit Kumar

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI a4 ,

       

      Share sample .pbix and screen shot of your table and vizualisation.

       

      Also you can try this

      RANKX(ALLSELECTED('salesorderdetails'[Trim name]),CALCULATE(ROUND([Gross Premium(USD) minus Commission (USD)],2)),,DESC,Dense)

       

      Regards,

      Harsh Nathani

      • a4's avatar
        a4
        Helper III
        6 years ago

        Hi Harsh,
        This formula also didn't work.Please find a acreenshot for better understanding

         

        [Gross Premium(USD) minus Commission (USD)] (Which is used in rank measure) = Net Premium (USD)

         

         

        Please suggest a better formula for optimization.

         

        Kind Regards

        Amit Kumar

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi a4 

    first, try to debug it.

    remove ALLSELECTED('salesorderdetails') and check performance

    then remove VALUES('salesorderdetails'[Trim name]) and check performance

    in order to find the biggest memory consumer.

    then we can try to suggest smth.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    a4 , Try like

    Rank = RANKX(ALLSELECTED('salesorderdetails'[Trim name]),ROUND([Gross Premium(USD) minus Commission (USD)],2),,DESC,Dense)

    • a4's avatar
      a4
      Helper III
      6 years ago

      Hi Amit,

      This formula doesn't give the desired result. All the ranks are same.

       

      Kind Regards

      Amit Kumar