Forum Discussion
Cumulative RANKX index COUNT OCCURANCE
I have a table
| ID | Column |
| 123 | 1 |
| 123 | 1 |
| 725 | 2 |
| 725 | 2 |
| 1003 | 3 |
| 1003 | 3 |
| 1003 | 3 |
| ID | Column |
| 123 | 1 |
| 123 | 2 |
| 725 | 1 |
| 725 | 2 |
| 1003 | 1 |
| 1003 | 2 |
| 1003 | 3 |
Hi, Anonymous , it's easier to achieve that in Power Query, as shown below,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVQdDmRqYotKGBgTE2RiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]), #"Sorted Rows" = Table.Sort(Source,{{"ID", Order.Descending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each {1..Table.RowCount(_)}}}, GroupKind.Local), #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "Count") in #"Expanded Count"If you insist on DAX, it can be also achieved after adding an index column,
Rank Column = RANKX ( FILTER ( 'RankTable', 'RankTable'[ID] = EARLIER ( 'RankTable'[ID] ) ), 'RankTable'[Index], , ASC )
5 Replies
- AlB
Community Champion
Hi Anonymous
This is best done in Power Query. Paste the following M code in a blank query to see the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVQdDmRqYotKGBgTE2RiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Column", each List.Numbers(1,[Count])), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Column"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}) in #"Removed Columns"If you want it in DAX you'll need to add an index column in Power Query first in any case.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- Fowmy
Super User
Anonymous
What is your actual scenario where you need to add a sequence column? In DAX, you need a value column to rank by.
If you just need a sequence number, Use power Query as AlB suggested.________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
- colacan
Resolver II
Anonymous Hi, Niiru, By introducing ID2 = ID+random decimal number, you can give rank to within each ID group. for example,
ID_Decimal = RankTable[ID] + RAND()then, tyr below.NewRank_Column =VAR currentID = RankTable[ID]VAR currentID_Decimal = RankTable[ID_Decimal]VAR ID_FilteredTable =FILTER (RankTable,AND ( RankTable[ID] = currentID, RankTable[ID_Decimal] < currentID_Decimal ))VAR RankRevised =COUNTROWS ( ID_FilteredTable ) + 1RETURNRankRevised 
CNENFRNLHi, Anonymous , it's easier to achieve that in Power Query, as shown below,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVQdDmRqYotKGBgTE2RiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]), #"Sorted Rows" = Table.Sort(Source,{{"ID", Order.Descending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each {1..Table.RowCount(_)}}}, GroupKind.Local), #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "Count") in #"Expanded Count"If you insist on DAX, it can be also achieved after adding an index column,
Rank Column = RANKX ( FILTER ( 'RankTable', 'RankTable'[ID] = EARLIER ( 'RankTable'[ID] ) ), 'RankTable'[Index], , ASC )
