Sort by Day of week

[Anonymous]

Hi when I chart some call volumes and also sales volumes I want to chart them by day of week. Meaning on a column chart I want the axis to read left ot right as 

Monday   Tuesday   Wednesday   Thursday   Friday   Saturday   Sunday

However the chart sorts in either the following orders

Friday   Monday   Saturday   Sunday   Thursday   Tuesday   Wednesday

OR in the reverse order of the above. This unhelpful and useless as I cannot ever see anyone wanting these orders.

Someone please help.

I have see one solution whereby a use added the the labels a number (1.Mon  2.Tues  3.Wed etc) to force the order numerically but this looks unprofessional and will only deter people who are not advocates of Power BI.

[Anonymous]

I understand this post is old. But my issue is that I follow all the suggestions from here and noting works. I have a DayWeek column and I added a sort column:

DayOfWeekSort = SWITCH( DimDate[DayName],

"Sunday", 0,

"Monday", 1,

"Tuesday", 2,

"Wednesday", 3,

"Thursday", 4,

"Friday", 5,

"Saturday", 6

)

When I select the visual, then the DayWeek in the Fields section, then under Modeling -> Sort by Column I pick the DayOfWeekSort and get a message: "The column can't be sorted by a column that is alredy sorted, directly or indirectly by this column". And I do not understand it since I did not do any other step to sort. I tried removing DayWeek altogether and then adding it and then doint sort by column. Nothing works. Really frustrating!

[marsheh]

Instead add a new column that is just the values 1 through 7.

On the 'Data View',

select the day of the week column.
With the day of the week column still selected, choose 'Sort by column' and select the new numeric column you just created with the values 1-7.

(still on the data view) Select, the new numeric column and change the data type and format of the column to a 'whole number'. 
Now your visual should sort correctly.

-M

[Anonymous]

@Anonymous , you're getting this error because you're creating the column based off the column it will eventually be sorting...it's a weird circular logic.

Best solution is to create the custom column using the Query Editor based on the code I shared earlier.  The column will sort just fine then.

Best,

~ Chris H

[Anonymous]

Third'd this. This error is very frustrating. It might have something to do with relationships between models? I am not sure but I would love to see a true solution.

[Anonymous]

Power BI always sorts text columns alphabetically unless you specify the sort order based on another column.  You need to have another column in your table that corresponds to the sort order you'd like. 

An easy way to do this is in query editor.  Add a Custom Column with the following formula:

Date.DayOfWeek( [<your date column>] )

This will generate a column where Sunday will return 0, Monday 1, Tuesday 2, etc.

Then, click on your day name column in Power BI.  Click on the Modeling tab of the ribbon.  Click on "Sort By Column".  Then choose the newly created column.

[Anonymous]

Sorry that is not the solution.

For example in the basic chart below I want the axis sorted Monday to Sunday. The solution offered gives me a column I do not use in the visual and therefore cannot sort by the day of week number.

As mentioned previously I could concatinate the labels to read 1. Monday, 2. Tuesday etc but i believe this is not a clean professional solution especially when trying to showcase to my work Power BI. 

[Anonymous]

Sorry, but you are mistaken.

You need to create the column to have in your data model.  You will NOT use that sort column in the visual.  You need to click on your Day Name column (the one you want in your visual).  Then click MODELING, then SORT BY COLUMN.  Then click the name of the sort column that you created.  Take a look at the screenshot.

Now your Day Name column will automatically sort by the other column, and you can use only the Day Name column in your visual as you intended.

[Anonymous]

Ok still not working.... I'll share with you the steps I have done cause perhaps I have done it wrong.

Firstly the column 

Date.DayOfWeek( [<your date column>] )

This formula the column errors it does not recognise DayOfWeek as a function?? Is this correct. The formula I went with was 

Day of Week = WEEKDAY(<my date column> , 2). This produced 1 for Monday through to 7 for Sunday

I than went to modelling and sorted by day of week

The visual still does not sort by day of week.

[Anonymous]

After doing change related to sorting under modeling tab, you need to select chart and then remove "DayName" column from axis and add it again, it will work 🙂

[Anonymous · Accepted solution]

First, the equation I gave uses the M language...in QUERY EDITOR.  Your DAX solution will work, but a calculated column will almost always take up more space and reduce performance.

Second, sort your [Day] column by your newly created [Day of Week] column.

Highlight the [Day] column

Click Modeling -> Click Sort By Column -> click [Day of Week]

[saud968]

This does not make any changes in the Matrix table

[Rajesh_Kumar_11]

Actually I have tried something easier and it worked for the same problem (How to arrange the Day names in order).

First Step: Go to the data view or report view select transform data to open the power query editor.

Second Step: Select the table which contains the <date> column in which you have to perform actions and Select Add Column tab and Select Custom Column option.

Third Step: In the New Column Name, name the column as you wish, here I have named it as <Day of week>. In the formula { = Date.DayOfWeek( [date] )  } (Instead of the undelined date, Select the <date> column that you have. (Not the <Day names>)). 

Fourth Step: Click the Home Tab and Select Close and Apply option to save the changes and close the power query editor.

Fifth Step: Go to the report view in the power bi desktop and select the <Day name> column name that you have. Select the Column Tools tab in above and select Sort By Columns option and select the new column that you have created <Day of week> it sorts the Day names in the number generated. Mostly it starts from Monday to Sunday (0 - 6). If you want to change the order you can go back to the query editor and select the <Day of week> column and change the formula like {  = Date.DayOfWeek( [date] , 2)  }, by increasing the number the order will change.

[Davidmg1982]

THANK YOU.