cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Post Patron
Post Patron

Re: How can I create a simple index column with dax.

Highlighted
Super User IV
Super User IV

Re: How can I create a simple index column with dax.

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
Super User IV

Re: How can I create a simple index column with dax.

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
Highlighted
Super User IV
Super User IV

Re: How can I create a simple index column with dax.

I created a calculation called Sequential once, but this is going to depend on what is in your table, can you share that or an example?

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Patron
Post Patron

Re: How can I create a simple index column with dax.

It is very simple. I have a column TOTAL sorted descending. Next to it I need an index starting with 1.

 

The table is created with DAX so I can't do it with query editor.

 

Giora

Highlighted
Resolver I
Resolver I

Re: How can I create a simple index column with dax.

Highlighted
Post Patron
Post Patron

Re: How can I create a simple index column with dax.

To my understanding it is a Rank. I want a simple index 1 up to the last row.

 

I'd like to have this:

ScreenHunter_006.jpg

Highlighted
Resolver I
Resolver I

Re: How can I create a simple index column with dax.

Apart from having an index part of the model or using RANK function (aligning it with the fact your net total is sorted) I am drawing a blank. Greg Deckler may have other suggestions.  There is an idea that may be of benefit to you that you may want to upvote

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19331770-row-number

 

Highlighted
Super User IV
Super User IV

Re: How can I create a simple index column with dax.

Oh, well that's super easy, you just need to add a column like this:

 

Index = RANKX(ALL('Table20'),[NetTotal])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Patron
Post Patron

Re: How can I create a simple index column with dax.

Highlighted
Super User IV
Super User IV

Re: How can I create a simple index column with dax.

Hooray!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Patron
Post Patron

Re: How can I create a simple index column with dax.

@Greg_Deckler  Greg, just now I noticed that RANKX ranks the same value as the same rank. I need something like row number.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors