Sort in filter by nested columns

[Sandra_aaA]

Hello,

I am trying to create a filter based on date where the year, quarter and day are nested. I would like the filter to be sorted to always show descending dates (2024, 2023, 2023; followed by Q4,Q3,Q2,Q1, followed by 30 July, 29 July, 28 July, etc.).

It only seems possible to apply a descending sort on one of the fields, but the other fields will automatically be sorted ascending. Is there a way around it?

[bhanu_gautam]

@Sandra_aaA , You can create a new Custom sort column

SortOrder =

       'Table'[Year] * 100000 +

       SWITCH('Table'[Quarter],

           "Q1", 3000,

           "Q2", 2000,

           "Q3", 1000,

           "Q4", 0) +

       (100 - 'Table'[Day])

Once you have the custom sort column, you need to tell Power BI to sort your date column by this new column.
Go to the Data view, select your date column, and then go to the "Column tools" tab in the ribbon.
Click on "Sort by Column" and select the custom sort column you created.

[Sandra_aaA]

Thanks for the answer @bhanu_gautam.

I am working with an ingested semantic model, so I am not able to add calculated columns. Is there an alternative solution for this? Something using a measure perhaps? (I doubt it, but doesn't hurt to ask!)

[bhanu_gautam]

Try using this measure

SortYear = MAX('Table'[Year])

SortQuarter =
SWITCH(
MAX('Table'[Quarter]),
"Q1", 4,
"Q2", 3,
"Q3", 2,
"Q4", 1
)

SortDay = 100 - MAX('Table'[Day])

SortOrder =
[SortYear] * 100000 +
[SortQuarter] * 1000 +
[SortDay]

[Sandra_aaA]

Thanks, but I have two issues with this solution:

1. The sorting order isn't actually correct. Is to be expected, because it still takes the Days in ascending order:

2. How do I use the measure to sort in a filter?