Forum Discussion
Incremental Number by Group using DAX
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
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"
1 Reply
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"