Forum Discussion

Cazpian's avatar
Cazpian
New Member
9 years ago
Solved

DirectQuery: Sort Data by Week Day

Hello,

 

My data is generated via a DirectQuery SQL statement, executed against a known database. I'm trying to order the data so that it appears as chronological weekdays and not ordered alphanumerically. The tool doesn't appear to allow me to add an order-by-clause within the query when editing the source, nor does it allow me to modify the sort order of the dataset via the Modelling ribbon tab within the main desktop UI. 

 

I'm summing the quantity information over the set and grouping by the day of the week. I have the day-of-year data in integer format so I could use this to organise the data, however it will only allow the first field of data for sorting within the Axis. What I need is to organise by Day-of-Year and display the Day-of-Week information.

 

Day-of-Year   => datename(dy, [Date])

Day-of-Week => datename(dw, [Date])

 

Any ideas greatly appreciated.

 

 

 

Need Help
  • Cazpian's avatar
    Cazpian
    9 years ago

    Hi,

     

    Thanks for your solution, it actually sent me in the right direction. My problem was simply that I assumed that the sort-order was at a table level and not a column level. I didn't realise that Microsoft Power BI allows you to set the sort orderat a column level.

     

     

    My issue was actually caused because the "Sort By Colmn" option appeared grey-out, I was selecting the table name, rather than selecting the column that I wanted to apply the sort order on. 

     

    Kind Regards,

     

    Tim

     

     

     

5 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Cazpian

     

    In your scenario, even in Direct Query mode, you can still create a WeekDay column with WEEKDAY() function.

     

    WeekDay = WEEKDAY(Table[Date],2)

     

     

    then just have your WeekDay Name column Sorted by WeekDay column.

     

    See my sameple (Month Name sorted by Month Number) below:

     

     

    Regards,

     

     

    • Cazpian's avatar
      Cazpian
      New Member
      9 years ago

      Hi,

       

      Thanks for your solution, it actually sent me in the right direction. My problem was simply that I assumed that the sort-order was at a table level and not a column level. I didn't realise that Microsoft Power BI allows you to set the sort orderat a column level.

       

       

      My issue was actually caused because the "Sort By Colmn" option appeared grey-out, I was selecting the table name, rather than selecting the column that I wanted to apply the sort order on. 

       

      Kind Regards,

       

      Tim

       

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This method worked for me with Direct Query. Sorting the column instead of entier table does the job. Thank you so much