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

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