Forum Discussion

badboy66's avatar
badboy66
New Member
8 years ago
Solved

Problem with rankx function

Dear all,   I've been thinking for hours now on the following problems but I couldn't come to a solution so far:     Ranking is done by the following formulas:   2_1_Test Ranking_UR_Test...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi badboy66,

     

    Here I update the formula as below.

     

    2_1_Test Ranking_UR_Test 2 = IF(IF(ISBLANK(Gesamtwerte[1_1_1 UR final_Test]),BLANK(),RANKX(ALL(Gesamtwerte),Gesamtwerte[1_1_1 UR final_Test],,ASC,Dense))<>BLANK(),RANKX(ALL(Gesamtwerte),IF(ISBLANK(Gesamtwerte[1_1_1 UR final_Test]),BLANK(),RANKX(ALL(Gesamtwerte),Gesamtwerte[1_1_1 UR final_Test],,ASC,Dense))))

     

    For the measure 2_2_2 ranking_UR increase final_Test, based on my test. I cannot achieve your goal here. 

     

    Regards,

    Frank

  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi badboy66,

     

    Based on my test, we can create a calculated table here. And create relationship between Gesamtwerte and the new table based on ID.

     

     

    Table 2 = SUMMARIZE(Gesamtwerte,Gesamtwerte[ID],Gesamtwerte[Name])

     

    Then we can create a measure to get the rank correctly.

     

    new rank = IF(ISBLANK([1_2 increase UR_Test]),BLANK(),RANKX(ALL('Table 2'),[1_2 increase UR_Test],,DESC,Dense))

     

    For more details, please check the pbix as attached.

     

    https://www.dropbox.com/s/08yu8jmaaod8hnn/Problem%20with%20rankx%20function.pbix?dl=0

     

    Regards,

    Frank

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
8 years ago

Hi badboy66,

 

Based on my test, you can update your formula of 2_1_Test Ranking_UR_Test like this, then you can get the result as you excepted.

 

2_1_Test Ranking_UR_Test = RANKX( ALLSELECTED( Gesamtwerte) , [1_1_1 UR final_Test],,ASC,Dense )

 

An enumeration that defines how to determine ranking when there are ties. You can get more details about tiles from the online document.

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/08yu8jmaaod8hnn/Problem%20with%20rankx%20function.pbix?dl=0

 

Regards,

Frank

 

 

badboy66's avatar
badboy66
New Member
8 years ago

Hi Frank,

 

Thank you for your reply

Sry, at the moment I don't have access to dropbox

 

I applied the changes mentioned in your reply above

 

Now it looks like that

 

 

The column 2_1_Test Ranking_UR_Test should show the blanks in column 1_1_1 UR final_Test as position 6 --> the two negative values should be ranked as 4 and 5

 

The ranking in column 2_2_2 ranking_UR increase final_Test still doesn't work

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    8 years ago

    Hi badboy66,

     

    Here I update the formula as below.

     

    2_1_Test Ranking_UR_Test 2 = IF(IF(ISBLANK(Gesamtwerte[1_1_1 UR final_Test]),BLANK(),RANKX(ALL(Gesamtwerte),Gesamtwerte[1_1_1 UR final_Test],,ASC,Dense))<>BLANK(),RANKX(ALL(Gesamtwerte),IF(ISBLANK(Gesamtwerte[1_1_1 UR final_Test]),BLANK(),RANKX(ALL(Gesamtwerte),Gesamtwerte[1_1_1 UR final_Test],,ASC,Dense))))

     

    For the measure 2_2_2 ranking_UR increase final_Test, based on my test. I cannot achieve your goal here. 

     

    Regards,

    Frank

    • badboy66's avatar
      badboy66
      New Member
      8 years ago

      Hi v-frfei-msft,

       

      Thank you for your contribution. It really helps me. Your formula works in the data set.

       

      Is somebody able to solve the issue with the last ranking column? maybe Greg_Deckler?

       

      Kind regards

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

        Hi badboy66,

         

        Based on my test, we can create a calculated table here. And create relationship between Gesamtwerte and the new table based on ID.

         

         

        Table 2 = SUMMARIZE(Gesamtwerte,Gesamtwerte[ID],Gesamtwerte[Name])

         

        Then we can create a measure to get the rank correctly.

         

        new rank = IF(ISBLANK([1_2 increase UR_Test]),BLANK(),RANKX(ALL('Table 2'),[1_2 increase UR_Test],,DESC,Dense))

         

        For more details, please check the pbix as attached.

         

        https://www.dropbox.com/s/08yu8jmaaod8hnn/Problem%20with%20rankx%20function.pbix?dl=0

         

        Regards,

        Frank