Forum Discussion
Need date sorting on bar graph
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.
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
Yes, I'm in the modelling view using the sort by function, selecting the sort by column from drop down.
Can you pls take a look visual and suggest? I've put a copy of pbix here
dropbox https://www.dropbox.com/sh/6kgldb1fhfichy4/AAAbXQhUr8VvyD65qTEOvIPFa?dl=0