cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cazpian
New Member

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.

 

 

week data.jpg

 

1 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.

 

ribbon.JPG

 

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

 

 

 

View solution in original post

5 REPLIES 5
v-sihou-msft
Microsoft
Microsoft

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

 

60.PNG

 

Regards,

 

 

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

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.

 

ribbon.JPG

 

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

 

 

 

View solution in original post

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
Frequent Visitor

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors