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 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?

 

 

 

Need Help

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    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'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]