Forum Discussion

mkr's avatar
mkr
Frequent Visitor
8 years ago
Solved

Ranking items DAX

 

Hi,

I have a table with Cycle Names and Dates. I would like to rank Cycle Name from 1 to N based on Max of Date. However, when I try to create ranking measure, the rank values are repeated if date value is the same. Is there any other way to have cycle name ranked/index using unique values?

 

Please note, that Power Query cannot be used, Live Connection is used to get data.

 

Thank you! 

 

Ranking for BL = RANKX(ALLSELECTED('Cycles'[Cycle Name]),CALCULATE([Max Of Date]), , ASC,Dense)

 

  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi mkr,

     

    Since there exists duplicate dates. You should know that the issue related to this Rankx() function. It cannot recognize which date comes first and which is second. So I'm afraid your requirement cannot be achieved with Rankx() function.

     

    Also, since you are using Direct Query mode. Are you using SQL Server Database? In my opinion, it will be much easier doing Row_Number() at SQL Server side than doing Rankx() at Power BI side.

     

    Thanks,
    Xi Jin.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    So, why are you ranking them 1, 2 if they have the same date, what is your tiebreaker/decision that tells you which one should be 1 and which one should be 2?

    • mkr's avatar
      mkr
      Frequent Visitor
      8 years ago

      Actually, in this case it doesn't matter which one should be first and which one - second. Is there any way to creat index? It is important to have it chronological though. Otherwise I will have to rethink the data model and try to create datetime column. 

       

      Thank you!

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        Hi mkr,

         

        Since there exists duplicate dates. You should know that the issue related to this Rankx() function. It cannot recognize which date comes first and which is second. So I'm afraid your requirement cannot be achieved with Rankx() function.

         

        Also, since you are using Direct Query mode. Are you using SQL Server Database? In my opinion, it will be much easier doing Row_Number() at SQL Server side than doing Rankx() at Power BI side.

         

        Thanks,
        Xi Jin.