Forum Discussion

gtamir's avatar
gtamir
Icon for Post Patron rankPost Patron
7 years ago
Solved

How can I create a simple index column with dax.

I want to create a simple index column starting with 1 next to the total column.  I can't do it with query editor because it is a calculated table.   Giora
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    gtamir  - Oh, then you want my SuperRank column formula, which I just invented:

     

    SuperRank = 
    VAR __table = FILTER(ALL('Table6'),[Total] > EARLIER('Table6'[Total]))
    VAR __tableSame = FILTER(ALL('Table6'),[Total] = EARLIER('Table6'[Total]) && [Customer] < EARLIER(Table6[Customer]))
RETURN
    COUNTROWS(__table) + 1 + COUNTROWS(__tableSame)

     

    Attaching PBIX, you want Table6.

  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    gtamir  That is correct as written. You have to have some secondary identifier, you cannot just magically assign the ranks when things tie. You could use actual Index column in your query and then use that as your secondary identifier and that would ensure that you would never get the same rank twice.

gtamir's avatar
gtamir
Icon for Post Patron rankPost Patron
6 years ago

Greg_Deckler It works BUT, if there are two identical names the index will be the same.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

gtamir  That is correct as written. You have to have some secondary identifier, you cannot just magically assign the ranks when things tie. You could use actual Index column in your query and then use that as your secondary identifier and that would ensure that you would never get the same rank twice.