Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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]
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:
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 , 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
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.
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.
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.
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.
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.
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 🙂
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]
This does not make any changes in the Matrix table
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.
THANK YOU.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
99 | |
94 | |
38 | |
30 |