Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
gtamir
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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

16 REPLIES 16
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

Anonymous
Not applicable

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

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

 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

🙂 Good one!

Hi @Greg_Deckler 

 

Please note that (Earlier) Is not supported by DAX now , try to check another solution.

It's still supported but it is deprecated.  Use variables instead.

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thank you for the quick response. I'll try it and try to understand.. 

I'll let you know.

@Greg_Deckler

Yep it works. Thanks

Hooray!



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.