Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
@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.
@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.
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
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
See if this solution helps:
https://community.powerbi.com/t5/Desktop/Add-calculated-index-column-by-DAX/td-p/72448
To my understanding it is a Rank. I want a simple index 1 up to the last row.
I'd like to have this:
Oh, well that's super easy, you just need to add a column like this:
Index = RANKX(ALL('Table20'),[NetTotal])
@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.
🙂 Good one!
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.
Thank you for the quick response. I'll try it and try to understand..
I'll let you know.
Hooray!
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
91 | |
88 | |
35 | |
35 |
User | Count |
---|---|
152 | |
99 | |
82 | |
63 | |
54 |