Forum Discussion
Need date sorting on bar graph
How to get dates sorting chronologically in bar graph?
Data set looks like this:
Tried to sort Date by cte_start_date I get error: "We can't sort the 'Date' column by 'cte_start_date'. There can't be more than one value in 'cte_start_date' for the same value in 'Date'. Please choose a different column for sorting or update the data in 'cte_start_date'. "
Added a unique column incrementing by one, called it 'n' and tried sorting Date by that and again got same/ similar error, even though only unique values in 'n'.
Also, made sure that in query editor I had sortined the Date column A-Z.
None of my attempts worked. I think sorting by 'n' column should have worked.
What is solution for sorting by Date?
Create a new column in your table using the following DAX, then use the column to sort your Date column. For more details, please review this modified PBIX file.
Column = DATE(YEAR(OutageTrending_hour[cte_start_date]),MONTH(OutageTrending_hour[cte_start_date]),DAY(OutageTrending_hour[cte_start_date]))
Regards,Anonymous MattAllington
The key to sorting a *text* date column by another column is that the column used for sorting is set to Datetype: Date or Datetype: Whole number.
8 Replies
The cardinality of the target column must match the sort column, and there must be a 1:1 match in values. E.g. If you want to sort Jan - Dec, you need a sort column 1-12. Or A-L would also work, but is less useful. So peek at your source column to see what you need. You can't do this in Power BI, you have to do it on load.
1. Does the column i am sorting by need to be used on the graph/visual?
2. I brought in two 'n' columns just for the sorting, so that I would have that 1:1 matching. That is, in SQL prior to loading to desktop, I sorted my dataset by date (In SQL I can do that even with duplicate Date values) and once they were in sorted order I applied n and n1 columns (incrementing by 1 starting with 1). I thought that would freeze, so to speak, the sort order. Then I brought it into desktop and reinforced sort order by hitting sort A-Z in query editor and sorted the n column by n1. Anyway, that too doesn't correct the sort order in the graph.
The dataset does have an order, but for a given record in my dataset there are always at least 24 hours of day sharing the same date (eg. 11-April appears 24 times, once for each hour of cte_start_date per record). Let's say I have just one row in my dataset recording an outage. It has a start date April 11, 2pm 2017 and end date of April 11, 8pm 2017. I want to show on bar graph precisely the hours that the application was down (6 hrs). I create a row for each hour of the day and pair it up with this outage record, inflating the number of rows tracking the outage to 24. I then create a column which I use to mark 0 for on and 1 for out-of-service for each of the 6 rows having times 2pm-8pm. Thus my visual knows to assign the bar 6 notches out of 24. In visual i am grouping by DD-MMM eg. 11-April.
SQL is able to sort hundreds of 11-Aprils to come before hundreds of 12-Aprils, 13-April, etc. But desktop can't?
No you don't need these columns in the visual
Are you setting "sort by column" in the modelling tab when in the Data view? That is where you make the change
Create a new column in your table using the following DAX, then use the column to sort your Date column. For more details, please review this modified PBIX file.
Column = DATE(YEAR(OutageTrending_hour[cte_start_date]),MONTH(OutageTrending_hour[cte_start_date]),DAY(OutageTrending_hour[cte_start_date]))
Regards,Anonymous thanks. I tried repeating your solution for two added date columns I want for hierarchy and having error.
Can all levels of hierarchy being sorted? Hierarchy is Year->MonthYear->DayMonth (previously called Date)
When I add another sort column (SortColumn2) using same formula and sort MonthYear by it, I get familiar error:
'We can't sort the 'MonthYear' column by 'SortColumn2'. There can't be more than one value in SortColumn2 for the same value in MonthYear.
I don't get this error since if that were true, first SortColumn wouldn't have worked.
filed shared here. Demo(1) https://www.dropbox.com/s/y2t60wuzn19cks5/Demo%20%281%29.pbix?dl=0
DayMonth is sorting:
lowest level date hierarchy is sorting
MonthDay is not sorting
What the error is really saying is all like items have to have the same sort id.
PK_Date Month Month_Name Month_Period_Of_Time Month_Period_Of_Time_Reverse
2017-06-28 00:00:00.000 2017-06-01 00:00:00.000 Jun 2017 1613 1281
2017-06-29 00:00:00.000 2017-06-01 00:00:00.000 Jun 2017 1613 1281
2017-06-30 00:00:00.000 2017-06-01 00:00:00.000 Jun 2017 1613 1281
2017-07-01 00:00:00.000 2017-07-01 00:00:00.000 Jul 2017 1643 1250
2017-07-02 00:00:00.000 2017-07-01 00:00:00.000 Jul 2017 1643 1250
2017-07-03 00:00:00.000 2017-07-01 00:00:00.000 Jul 2017 1643 1250We have a time table in sql.. we found this magic sql somewhere that sets the sort values correctly. For dates, you might have to strip the time off.
--Month
UPDATE dbo.Time SET Month_Period_Of_Time = RowNumber
FROM dbo.Time TM, (SELECT [Month], ROW_NUMBER() OVER (ORDER BY [Month]) AS RowNumber FROM dbo.Time) AS TMR
WHERE TM.[Month] = TMR.[Month]
UPDATE dbo.Time SET Month_Period_Of_Time_Reverse = RowNumber
FROM dbo.Time TM, (SELECT [Month], ROW_NUMBER() OVER (ORDER BY [Month] DESC) AS RowNumber FROM dbo.Time) AS TMR
WHERE TM.[Month] = TMR.[Month]Anonymous MattAllington
The key to sorting a *text* date column by another column is that the column used for sorting is set to Datetype: Date or Datetype: Whole number.
