"Sort by column" not working if column is set as a key column

[DmitryKo]

The setup is usual, e.g. a status table where status names have to be sorted (on visuals) according to their positions in the process. For example table "D_Status":

Status	OrderNum
New	1
In Progress	5
Done	100

Now if we go go the table properties, select "Status" column, expand "Advanced", and in "Sort by column" select "OrderNum" column - it would sort by OrderNum (numeric) instead of alphabetical sort by Status (text).

Thing is, this stops working the moment you set "Status" column as a "Key column" in a table's properties. Just try changing this one setting on and off, and see sort by other column switching between working and not working.

Why?

[lbendlin]

A key column is assumed to be unique and distinct, and one of its main purposes is serving as an index by sorting the rows (sometimes even physically, in case of a clustered index). Having the key column sorted by another column defeats that purpose.

[DmitryKo]

"Having the key column sorted by another column defeats that purpose."

In that case, PBI D's UI should disallow using "Sort by column" feature for a column configured as table's key column, and/or give a warning when trying to do so. In the current state, it silently allows creating configuration that simply does not work as expected/advertised.

Being unique and distinct should not prevent anyone from sorting table's data by any other column (which is the reason the feature exists) if required by business scenario; it was never an obstacle with any relational (e.g. SQL) sources for years. It's a mystery why such a buggy implementation exists in PBI.

[lbendlin]

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .