How can we apply a custom (not alphabetical) sort order to ANY FIELDS in our data model?
Today I would like to present two “issues” that I reckon many of you faced before.
The first one is sorting month names chronologically in a custom Calendar/Date table, and the second one is sorting customers based on a custom sort order.
Month Name Sorting
So, you have followed a Power BI Best Practice and created your own custom-made Calendar table, but when you drag the month name field to a visual and it looks something like this:
I think we can all agree that this is sub-optimal. In other words, it’s not going to help report users to reveal insights – or at least not at a historical or trend level. More importantly, doesn’t matter how many times you click on the header (Month Name) the sort order won’t get automatically “fixed”.
By fixed I mean that Power BI won’t sort months chronologically
How can we fix this problem?
Surprisingly it is a relatively simple fix – and the solution can be expanded to other fields (or any fields) in your data model.
We need to “add” another field to our calendar table that will drive the custom sort order. When it comes to a Date Table it is relatively easy; just utilise the MONTH() DAX function over the Date field and Bob’s your uncle! Something like this:
Calendar =ADDCOLUMNS (
CALENDARAUTO (),
“Month Name”, FORMAT ( [Date], “Mmmm” ),
“Month No”, MONTH ( [Date] )
)
Once that’s done you can select the Month Name field in your model and under Column tools click on Sort by column. Then select the newly created “Month No” field.
This step will immediately fix your sorting problems! This means that your months are now sorted chronologically – as they should be.
But it doesn’t stop here!
As I mentioned in the intro the same logic can be applied to or expanded to ANY OTHER FIELDS in your report.
What do I mean by that?
Let’s say that you have five Customers and for a particular analysis you want to sort them based on a custom sort order; not based on a measure (for eg Sales), nor alphabetically.
Like below:
Once again, it doesn’t matter how many times I click on the Customer header, the sort order never going to be the same as the custom-made I have next to them.
And the solution?
Just like above, for the Month Name, I can select the Customer field, head over to Column tools, and select the Sort Order field to create a customised sort order for my customers.
Things To Remember
Using this method, we can easily change the sorting for any fields in our data model based on our needs. However, to be able to do that we must have a dedicated field that Power BI can use as the basis for that sorting. In some cases, it may result in an additional Dimension table which also means that your beautiful Star Schema may look more like a Snowflake 🙂
And one more thing! While it is not a drag and drop solution like in a Pivot table in Excel, once you create a custom sort order for a field you can be 100% sure that users cannot accidentally break or change this order.
Roland
🙏Thanks for watching and if you liked this video please hit the 👍 button.
📢 Also be sure to subscribe to the channel so you never miss a new video.
0 Comments