Search code examples
powerbidaxpowerbi-desktop

Why do I get "Sort by another column" error Power BI

I do not understand why do I get this error?

enter image description here

I know for sure that column MM-DD Sort has a unique values in a table

This is my query for calendar table that I am using in Power BI:

 DECLARE @MinDate DATE = '2017-07-01',
        @MaxDate DATE = CAST(GETDATE() + 7  as DATE)  
;WITH cte_Calendar AS (
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b
) SELECT Date,
         MonthNum = MONTH(Date)
         ,Year = Year(Date)
         ,Month = FORMAT(Date, 'MMM')
         ,'MM-DD' = FORMAT(Date, 'MMM') + '-'+ FORMAT(Date, 'dd')
         ,'MM-DD Sort' = FORMAT(Date, 'yyMMdd')

FROM cte_Calendar

So is it some kind of a bug in Power BI?

Solution

  • The values in MM-DD Sort are unique but the values in MM-DD are not.

    For example, if your date table spans 2019 and 2020, then you'll have Jun-30 in multiple rows with 190630 and 200630 in the sorting column. So Power BI doesn't know which index to use for sorting purposes.

    You can use your suggested sorting column for dates (which do include year) but since there is no year in MM-DD, then you can't include the year in MM-DD Sort and expect it to work.