Incremental Number by Group using DAX

[nanoshi]

Hi,

I'm trying to add an incremental index calculated by a group like that:

Key	Row_Id
123prod1_2016	1
123prod1_2016	2
123prod1_2016	3
456prod2_2017	1
456prod2_2017	2
456prod2_2017	3

 I found this function but doesn't work (the output is 1 for all rows):

=RANKX(FILTER(Table;EARLIER(Table[Key])=Table[Key]);Table[Key])

Thanks for the help

[ricardocamargos · Accepted solution]

Hi @nanoshi,

You can do that using M Language,

Just click on Edit Queries and Advanced Editor and type this code replacing the table name/source

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyjg8I8ndRitXBxzExNSPIiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}}),
#"Group" = Table.Group(#"Changed Type", {"Key"}, {{"Rows", each Table.AddIndexColumn(_,"Index", 1), type table}}),
#"Expanded" = Table.ExpandTableColumn(#"Group", "Rows", {"Key", "Index"}, {"Rows.Key", "Rows.Index"}),
#"Removed Columns" = Table.RemoveColumns(Expanded,{"Rows.Key"})
in
#"Removed Columns"