Forum Discussion
Index column without sorting
Is it possible with dax to create a custom column with an index (basically a row number), I need not to rank by a column and mantain the order passed by the DB.
Is then possible in the same order to calculate another Index that starts from 1 for each type of literal description in a column? (eg. Red, Green, Blue)
Ok, so for DAX is impossible to add an index column in the original order of the query, the only way is using power query.
Thanks
2 Replies
You will need a column that can be ordered. Otherwise, there is no way for DAX to count up in terms of how many rows into the dataset it is. If you do not have this, just add an Index column in Power Query. If you had that, it becomes simply a matter of using DAX Index = COUNTROWS(FILTER('Table',[Index]<EARLIER([Index]))
Of course, if you already have it...
Now, if you add color, you can do this:
DAX Index 2 = COUNTROWS(FILTER('Table',[Index]<EARLIER([Index]) && [Color] = EARLIER([Color]))
You might find this entertaining: https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/td-p/509739
Ok, so for DAX is impossible to add an index column in the original order of the query, the only way is using power query.
Thanks
