topic Re: Index column without sorting in DAX Commands and Tips

Index column without sorting

AGo — Sun, 05 Apr 2020 18:16:07 GMT

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)

Re: Index column without sorting

Greg_Deckler — Mon, 06 Apr 2020 02:25:16 GMT

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

Re: Index column without sorting

AGo — Mon, 06 Apr 2020 09:14:25 GMT

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