Forum Discussion
Problem with sort order changing after load into model
Hi iplaygod
The Vertipaq engine applies a number of compression algorithms to reduce the memory footprint of a Power BI data model.
See here for a good description.
In your example, it could be Run Length Encoding that is resulting in a reordering of your table's rows. With Run Length encoding, a column with a large number of repeated values is sorted so that equal values are grouped together. This means rows containing a common value can be internally referred to by start position & run length.
For this reason, you can never rely on a particular sort order of rows in a Power BI model.
By and large, there is almost no way in DAX to write a useful expression that depends on the order of a table's rows (exceptions may be the SAMPLE function and the undocumented TOPNSKIP function). I would always determine "order" from the values contained in one or more columns.
In the Data view, you can sort by a particular column by right-clicking on a column header, which might meet your needs. I'm not sure whether this is cosmetic or actually changes the sort order of the in-memory table.
Regards,
Owen
You're welcome :)
You've hit the nail on the head - in order to enforce some sort of order, you need a column containing values that define the order, such as an Index column, a Date column, or some other column of sortable values.
Once you have such a column, you can use DAX to iterate over or filter those rows appropriately, but the details will depend on exactly what you are trying to do. If you can post the details here or in a new thread then I or someone else can help further :)
Best regards,
Owen
I have also faced a similar issue. Could you please take a look at the issue in the link below -