Forum Discussion
Stacked graph sort order
The good thing about adding a column in M (Edit Queries) or DAX (Data view) is that once you add the column, it will be there for any new data you import when you refresh the report. As long as you're refreshing and not starting your entire Desktop report from scratch every time, any new columns will be there.
However, you can also do this with the related table. If your main table looks like this (the Scores column is just something to add data in there for the chart later):
And your Aux table has unique values and the desired sort order like this:
You can create a relationship between them on the Column Values column. Power BI will probably have already created it for you, really. Check in Manage Relationships in the Home tab of the ribbon.
With that in place, go to the Data view and add a New Column to the main table.
Sort Order = RELATED(AuxTable[Sort Order])
Use that new column with the Sort By Column feature, and you're good to go. Notice that 8_A in the chart below falls between 8 and 9, based on the sort order provided in the table. Of course, you're still adding a new column with this method, but again, it will be there when you refresh.
You can use Sort By Column in the Modeling tab of the ribbon, while in the Data pane on the left-hand side. This will allow you to have one column's values define how a different column is sorted. You'll first need to add a column that defines your order. Check out this post for details on how to do this.
The thing is that I would prefer not to add any column to set that order (as my report is based on some excel files and each time I would receive a new updated version, would have to insert that same column...)
Can it be done based on an aux table, linked to this one?
Example:
Aux table with
8_A - 1
8 - 2
9 - 3
Select the column on the original file and sort by the column with the order on the aux table.
Is it possible?
Thank for your help!
The good thing about adding a column in M (Edit Queries) or DAX (Data view) is that once you add the column, it will be there for any new data you import when you refresh the report. As long as you're refreshing and not starting your entire Desktop report from scratch every time, any new columns will be there.
However, you can also do this with the related table. If your main table looks like this (the Scores column is just something to add data in there for the chart later):
And your Aux table has unique values and the desired sort order like this:
You can create a relationship between them on the Column Values column. Power BI will probably have already created it for you, really. Check in Manage Relationships in the Home tab of the ribbon.
With that in place, go to the Data view and add a New Column to the main table.
Sort Order = RELATED(AuxTable[Sort Order])
Use that new column with the Sort By Column feature, and you're good to go. Notice that 8_A in the chart below falls between 8 and 9, based on the sort order provided in the table. Of course, you're still adding a new column with this method, but again, it will be there when you refresh.
You rock!
Thank you very much :)
You're welcome andrerosario!
I have the same problem as andrerosario but can't understand KGrice's solution. Would someone be willing to unpack this step by step for a novice?
