Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hxkresl
Helper V
Helper V

Need date sorting on bar graph

How to get dates sorting chronologically in bar graph?  

Capture.PNG

 

Data set looks like this: 

Capture1.PNG

 

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?

2 ACCEPTED SOLUTIONS
v-yuezhe-msft
Employee
Employee

@hxkresl,

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]))
1.PNG


Regards,

 

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@v-yuezhe-msft @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.

 

 

View solution in original post

8 REPLIES 8
synergised
Resolver II
Resolver II

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    1250

 

We 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]

 

@v-yuezhe-msft @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.

 

 

v-yuezhe-msft
Employee
Employee

@hxkresl,

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]))
1.PNG


Regards,

 

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft  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 sortinglowest level date hierarchy is sorting

 

MonthDay is not sorting

Capture1.PNG

 

 

 

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.