Forum Discussion

hxkresl's avatar
hxkresl
Advocate III
9 years ago
Solved

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_sta...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

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


    Regards,

     

  • hxkresl's avatar
    hxkresl
    9 years ago

    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.

     

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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


Regards,

 

hxkresl's avatar
hxkresl
Advocate III
9 years ago

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