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
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
I should have suspected that is the way Power Pivot optimizes its data, but it's pity you cannot easily overrun it (with consciousness it may increase the size of the data model significantly)...
You suggest that DAX is of any use when we want to build measures that rely on order.. does it mean that if I want to have such metrics I need to use Excel spreadsheet?