Forum Discussion

Pfutch1's avatar
Pfutch1
Frequent Visitor
1 year ago

SortByColumn property set to an invalid column ID

I am trying to sort my Week-Year column of my date table, by a column called "Sorting Order - Weeks". However, for this specific column I am getting the following error. "The column "Week _ Year (...
Need Help
Pfutch1's avatar
Pfutch1
Frequent Visitor
1 year ago

Dax Table Code 5 (Final, has return clause)

RETURN
    SELECTCOLUMNS (
        CompleteCalendar,
        
        -- Base date columns
        "Date", [Date],
        "Date Key", [DateKey],
        "Date Today", TODAY(),
        "Day of Month", [Day of Month],
        "Day of the Week - Format: (#)", [WeekDayNumber],
        "Week Day - Format: (MMM) ", [Week Day],
        "Sequential 365 Day Number", [Sequential365DayNumber],

        -- Calendar = Solar Calendar (January-December)
        "Year II - Format: (YYYY)", [Calendar YearNumber],
        "Year I - Format: (YYYY)", [Calendar Year],
        "Quarter of year - Format (#.##)", [Calendar QuarterNumber],
        "Quarter of Year - Format: (Q#)", [Calendar Quarter],
        "Sorting Order - Year and Quarter", [Calendar YearQuarterNumber],
        "Quarter of Year - Format: (Q# YYYY)", [Calendar Quarter Year],
        "Month of Year - (Number Only)", [Calendar MonthNumber],
        "Month of Year - Format: (MMM)", [Calendar Month],
        "Sorting Order - Year and Month", [Calendar YearMonthNumber],
        "Month of Year - Format: (MMM YYYY)", [Calendar Month Year],
        "Week of Year I - Format: (##)", [Calendar WeekNumber],
        "Week of Year II - Format: (W##)", [Calendar Week],
        "Sorting Order - Weeks", [Calendar YearWeekNumber],
        "Week + Year (Order) - Format: (W## - YYYY)", [Calendar Week Year],
        "Year + Week (Order) - Format: (YYYYWK)", [Calendar WeekYearOrder],
        "Relative to - # of Weeks to the Current Month", [Calendar RelativeWeekPos],
        "Relative to - # of Months to the Current Month", [Calendar RelativeMonthPos],
        "Relative to - # of Quarters to the Current Quarter", [Calendar RelativeQuarterPos],
        "Relative to - # of Years to the Current Year", [Calendar RelativeYearPos],
        "Month - Starting Date", [Calendar StartOfMonth],
        "Month - Ending Date", [Calendar EndOfMonth],
        "Quarter - Starting Date", [Calendar StartOfQuarter],
        "Quarter - Ending Date", [Calendar EndOfQuarter],
        "Year - Starting Date", [Calendar StartOfYear],
        "Year - Ending Date", [Calendar EndOfYear],
        "Days - Total Days Per Month", [Calendar MonthDays],
        "Days - Total Days Per Quarter", [Calendar QuarterDays],
        "Days - Total Days Per Year", [Calendar YearDays],
        "Day of Month - Format: (##)", [Calendar DayOfMonthNumber],
        "Day of Quarter - Format: (#)", [Calendar DayOfQuarterNumber],
        "Day of Year - Format: (#)", [Calendar DayOfYearNumber],
        "Date - Previous Week", [Calendar DatePreviousWeek],
        "Date - Previous Month", [Calendar DatePreviousMonth],
        "Date - Previous Quarter", [Calendar DatePreviousQuarter],
        "Date - Previous Year", [Calendar DatePreviousYear],

        -- Holidays and working days
        "Holiday Name", [Holiday Name],
        "IsWorkingDay", [IsWorkingDay],
        "Day Type", [Day Type],
        "Is Past", [Is Past],
        "Is Past Prior", [Is Past Prior]        
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Pfutch1 

     

    Thanks for the reply from tackytechtom .

     

    I also tested it, and it can be sorted without any errors. Have you performed other operations on this calculation table? My suggestion is that you can try to create a new pbix, and then create the same calculation table to rule out whether it is a problem with the current pbix.

     

    But I also encountered the same problem mentioned by tackytechtom . When the week number columns are the same, they will be sorted according to "week number + year number (sequence) - format: (W##-YYYY)". The solution I found is to change the format of "week number + year number (sequence) - format: (W##-YYYY)" to "YYYY-W##".

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.