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
Thanks alot for that information.
I understand now why the sort order is different after loading it.
It is because the sort order is determined by the engine to optimize memory storage in the model, not to preserve any "original" sorting of the table itself.
I can see on the table in the data view that repeated values are sorted next to each other, probably as you say, to provide for better compression rations. This then "screws up" the sort.
I wish there were some way of getting around that as it does set some limitations on what I can do with the code / formulas...
Do you have any good tips for "faking" sort order in order to preserve similar functionality AS IF i had the table sorted by, lets say, Row ID ?
Lets say I want to write a formula where I iterate over a set of rows, and i want to be SURE that the rows i iterate over have an increasing Row ID number ? I can repost that in a different thread if i have to but it is actually the reason i asked the original question...
?
Thanks for the help
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 -
