Cumulative RANKX index COUNT OCCURANCE

[Niiru1]

I have a table

ID	Column
123	1
123	1
725	2
725	2
1003	3
1003	3
1003	3

Column = RANKX('Full Table',[ID],,ASC,Dense),

is there a way to count the occurances:

ID	Column
123	1
123	2
725	1
725	2
1003	1
1003	2
1003	3

[Anonymous]

@Niiru1  Hey Mate,

You can use below one . You have to create a column to get number of occurance 

Occurence = 
COUNTX (
    FILTER ( 'Full Table', EARLIER ( 'Full Table'[ID] ) = 'Full Table'[ID]  ),
    'Full Table'[ID] 
)

 

Thank you mates @Niiru1 , @CNENFRNL , @Fowmy  , @AlB 

[CNENFRNL · Accepted solution]

Hi, @Niiru1 , 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
)

[colacan]

@Niiru1  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 ) + 1

RETURN

RankRevised

[Fowmy]

@Niiru1 

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 🙂

YouTube  LinkedIn

[AlB]

Hi @Niiru1 

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