Problem Description:
I’m encountering difficulties with sorting date columns in Power BI, and I’m seeking assistance to resolve this issue. Specifically, I have two columns in my dataset related to task due dates: ‘task_due_text’ and ‘task_due_sort’. When attempting to sort ‘task_due_text’ based on ‘task_due_sort’, I encounter an error stating, “We can’t sort the ‘task_due_text’ by ‘task_due_sort’. There can’t be more than one value ‘task_due_sort’ for the same value in ‘task_due_text’.”
Despite ensuring the unique correspondence between the two columns, I’m experiencing difficulties in sorting them properly in Power BI.
task_due_text task_due_sort
Nov 2026 202611
Dec 2025 202512
Nov 2025 202511
Jan 2025 202501
Dec 2024 202412
Nov 2024 202411
Oct 2024 202410
Sep 2024 202409
Aug 2024 202408
Jul 2024 202407
Jun 2024 202406
May 2024 202405
Apr 2024 202404
Mar 2024 202403
Feb 2024 202402
Jan 2024 202401
Dec 2023 202312
Nov 2023 202311
Jan 2023 202301
task_due_text = FORMAT('Task Table'[task_due_date], "mmm yyyy")
task_due_sort = YEAR('Task Table'[task_due_date]) * 100 + MONTH('Task Table'[task_due_date])
Steps Taken:
- Verified the unique correspondence between ‘task_due_text’ and ‘task_due_sort’ to ensure data integrity.
- Checked for hidden characters or whitespace in the ‘task_due_sort’ column that might affect sorting.
- Confirmed that both columns have compatible data types and match their intended use – ‘task_due_text’ is set to Text and I’ve tried both Text and Whole Number data types for ‘task_due_sort’ with no change in behavior.
- Experimented with sorting both columns independently to understand the behavior – I can sort ‘task_due_sort’ based on ‘task_due_text’
Desired Outcome:
I’m seeking guidance on resolving the sorting issue and achieving the desired sorting behavior in Power BI. Ideally, I want to sort ‘task_due_sort’ based on the numerical representation of the date while maintaining the unique correspondence with ‘task_due_text’.