Power BI: descending order on date hierarchy slicer

Hello!

Recently I had a tricky question:

"Michal, how can you set descending order for each level of hierarchy in slicer?" The idea was to show newest date at the top of slicer on each level. At first I thought it's kind of obvious!

And yes, here's question to Microsoft: why don't we have any kind of button on slicers like "reverse order for each hierarchy level"? It would make life so much easier.

Instead, we need to create a workaround and create sorting column for each level of hierarchy. Which is standard for any text values. But in terms of dates it's quite non-intuitive in my opinion.

Let me show you how I approached that task.

Agenda

• Business case
• Sample of data and goal
• Challenge
• Solution
• Final thoughts

Business case

We want to show hierarchy of dates in descending order. So customer can get down from year to day level but newest data is always on top.

Sample of data and goal

In this scenario we only have one table.

We have dates from 1st Jan 2023 until today (18th Feb 2024).

This is what we want to achieve:

Challenge

We have default sorting options that can be applied to filter. But only from certain level:

Basically, the selected "sort by" level will apply descending/ascending order, but other levels will be sorted by default from A to Z, or from 1 to last number, depending on data type. An example of descending sorting by year:

Looks good, but if we dive deeper, to month level:

We got defualt alphabetical order (A-Z). Same goes for lower levels in hierarchy.

Now let's switch descending order to month name:

We will get sorting from Z-A (it's still wrong because months should not be sorted by alphabet, but it's just to prove a point), but year will be sorted by default (from 1 to last number as this is whole number data type). It simply means that descending or ascending sort cannot grab all levels, just the selected one.

Solution

How to fix above issue? Each column in a table, can be sorted by another one. So, for example if we sort month name column by a column with a number, where:

• January = 1
• February = 2, etc

and so on, then this will be applied as default sorting.

Step 1

You can create such columns either in DAX or Power Query, depending on situation. In our case, let's check Power Query. Here is the full code (so basically, the table imported from excel is just 1 column with date and everything else is build in Power Query):

If you would like to receive this code in flat file, message me via linkedin or drop a comment.

Step 2

Now if we go back to our table, we can set up sorting as we like.

Month name

1 - choose column you want to sort

2 - go to column tools

3 - choose column by which you want to sort

4 - in our case we have two options, "Month number" for ascending and "Month number reverse" for descending

Day

Same case as month name, but we go to day column. In this case we can leave default day number sorting. As this is number it will be sorted from 1 to highest. Or we can choose "Day number reverse" if we want to sort descending by default.

I didn't create reverse sorting for year as this is our top level in this case, so we can simply sort descending on slicer by year.

Anyway, if we apply following sorting:

• Year - descending on slicer level
• Month name - sorted by "Month number reverse"
• Day - sorted by "Day number reverse"

This is what we get:

Each level is sorted from top to bottom. No matter if it's number or text. As sorting is based on columns we created.

In slicer fields, we have columns as follows:

What if instead of a number of day we would like to have actual date?

Something like:

In this case we can use RANKX() DAX function to create a column with reversed sorting and follow same approach as with day/month name (sort by created column). Here is DAX:

Rank = RANKX(calendar_table,calendar_table[date],,DESC,Dense)        

Warning!

You can't sort underlying column used in RANKX() by created column or you will have a circular dependency error:

This is why I created a duplicated date column for filter (check last step in Power Query code above). Now I can use duplicated column ("date for filter") on slicer and sort by "Rank" column with RANKX() DAX function:

Here are fields of a slicer:

Warning!

You need to be careful when creating sorting columns. This can be tricky especially in custom text year-month combination fields.

Different number values cannot be assigned to same value, otherwise, Power BI will drop an error:

In this scenario it's best to scan through both columns:

• one you want to sort
• and another one you want to use for sorting

As above, you can see that for March we have assigned number 2 and number 3. Which needs to be fixed in the code or mapping.

Final thoughts

Well, that's it for today!

Sorting by column can be a bit tricky at start. But I find it very useful in many projects.

Above example can be applied also to sort items on axis across different visuals and different business text values.

As always, have a great week ahead.

In case of any questions let me know!