Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iplaygod
Resolver I
Resolver I

Problem with sort order changing after load into model

I have a problem that is related to sort order. Its driving me crazy. cant figure it out.

 

I have an access db in file format. A table contains approx 2 million rows of data. Each row has a Row ID field, that autoincrements from 1 and up.

I choose the db as a source and load the table.
And in the "edit query" window, the sort order seems ok. Ie the first row at the top of the preview window has a Row ID of 1.

 

Then I click Close and Apply, the rows are loaded into the model and then I arrive at the Data view where i can also look at the table rows. However, in this view, the sort order is completely screwed up.
The top row in the window (what should be the first row) has a Row ID of 353080. (?!?!?)

 

So whats going on? why is the sort screwed up?
The ONLY thing i did was load the table into the model. Nothing more nothing less. Yet the sorting seems to have changed after the load... ?!

 

I have also tried going back to the "Edit Queries" mode and explicitly sorted on the Row ID. But When I close and apply, the result is still the same in the data view.

 

Is this a bug? Or am I missing something?
Please let me know if there is some way of preserving the sort order correctly after loading the table.

thank you very much!

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

5 REPLIES 5
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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?

@OwenAuger

 

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

@iplaygod

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

I have also faced a similar issue. Could you please take a look at the issue in the link below - 

https://community.powerbi.com/t5/Desktop/Sort-order-in-power-query-and-data-view-DAX-for-grouping-id...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.