Preserving order of (sorted) keys, not the order of values in tables and charts

[efglynn]

Why can’t there be a simpler way to control sort order in tables and charts?  Much like I select a field to specify the data, why can’t I specify (easily) a field that is not directly part of the graphic to control the sort order?

In my current problem, my data model has 7 tables, with a 1:N relationship between the AgeBracket dimension and a Surgery fact table.  The data model connects AgeBracketID in the AgeBracket table to AgeBracketID in the Surgery table.

My stacked column chart is automatically sorted alphabetically by the AgeBracketName from the AgeBracket table when I want it sorted by the AgeBracketID (the key), which is not specified as part of the displayed graphic.

I don’t want either of the sort options for the graphic that I’m given now:  “Sort by Count of Rows” and “Sort by AgeBracketName.”  I want the sort order to be by AgeBracketID (the sorted key), that puts the names in chronological order instead of alphabetical order.  But the key is not one of the fields in the graphic.

The info in this recent article “Sort by column in Poser BI Desktop” doesn’t seem to work for me when I have a number of related tables in the data model.  Did I miss something?

I seem to have similar sort problems with about any chart or table I create in Power BI when text to be displayed is from a dimension table found via the key in the fact table, and the order of the keys is what I want preserved even though they are not shown.

I'm new to PowerBI, so any help is appreciated.

[efglynn]

I learned there is no ordering problem when linked live directly to a cube from the data warehouse.  Somehow metadata from the cube preserves the intended order of items in the stacked column chart. 

The problem above remains when the cube is rebuilt in PowerBI by importing the original data warehouse tables.  Apparently part of the metadata from the cube definition is lost when importing tables.   A stacked column chart using the key from the dimension table as the X-axis places the columns in desired order in the column chart, but the labels are the keys, not the desired labels from the dimension table. 

I'm told part of the cube definition includes the data specification and sort order specification separately.  Something like that is needed inside PowerBI so one can more easily control the order in a stacked cube instead of getting alphabetical order of the values.

[v-yuezhe-msft · Accepted solution]

Hi @efglynn,

In your scenario, click AgeBracketName of AgeBracket table in the Fields pane, and click “Sort by column ” to choose “AgeBracket” , you should able to sort the visual as expected. Here is an example for your reference.


If the issue still occurs, please share sample data of your tables for us to analyze.

Thanks,
Lydia Zhang