Forum Discussion

Sandra_aaA's avatar
Sandra_aaA
Regular Visitor
2 years ago

Sort in filter by nested columns

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...
Need Help
Sandra_aaA's avatar
Sandra_aaA
Regular Visitor
2 years ago

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's avatar
bhanu_gautam
Super User
2 years ago

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's avatar
    Sandra_aaA
    Regular Visitor
    2 years ago

    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?