DirectQuery: Sort Data by Week Day

[Cazpian]

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.

[v-sihou-msft]

@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,

[RushiS]

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

[Cazpian · Accepted solution]

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

[edejes2]

you can do this as well with the days, hope you like it https://www.youtube.com/watch?v=n_gnuFS8qoY&feature=youtu.be

[RushiS]

This is not working for Direct Query as Modeling tab disabled when staorage mode set to Direct Query