Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a line chart with data showing number of X vs date. However, I have a problem where two of the months are flipped (see image). I have looked into this community and found similar threads. The solution I read many times was to sort my time by the date column. However that has not worked for me. Someone mentioned changing the x-axis to conitnuous but my chart won't allow me to do that and I don't have a dates table as some mentioned is need for continuous x-axis. Does anyone have any other solutions for this issue?
Solved! Go to Solution.
Ok. without something to work with it is difficult to assist. But let me try one more thing.
Download this PBIX file. It has some random data I put together for 2020. Notice how the chart is not sorted by month properly. I'm not sure how it is sorted. It isn't alphapbetical or by date. Do the following:
Ensuring the chart is sorting correctly.
It should now be sorted by the Month Name, which in turn is sorted by the numerical month number.
See if walking through that process helps. I don't know what else to do if I cannot have access to your PBIX file to more closely examine the issue.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @jhern90 ,
Go to "edit queries">"Add column">"Index Column ">" from 1":
Then go back to data view>choose date column>sort by [Index]:
This would help.
Best Regards,
Kelly
@v-kelly-msft , I tried your suggestion but couldn't make it work. When I tried to sort the date column using the index column I got an error saying that I cannot sort it because there are multiple values in the index column for the same value in the date column. My data has multiple rows with the same date. Maybe this is my problem. Unfortunately I cannot get rid of them since they are different records, the other columns have different values, they just happen to have the same date. Do you know of a workaourd for this?
Hi @jhern90 ,
What is the data type of your column?Is it a date type?
@v-kelly-msft , it is a date type. I've tried all the solutions above but none worked. It seems to be the problem that I have multiple dates that are the same and that's causing it to not sort properly, but not sure why that's being a problem.
@jhern90 the month field in your date table should not be a Date type. A month number is an integer.
The Sort By Column solution works. This isn't a workaround. this is how Power BI and the data model in Excel's Power Pivot are designed. Can you please post some sample data in a PBIX file and share via OneDrive so we can see exactly what your issue is? I am thinking this is a 5 minute fix but we've been guessing what your data model looks like for two weeks. I'd really like to help you move this project forward. If it is confidiential data you don't want to post, but you feel comfortable sharing with me (or someone else in this thread) please share via PM.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans, unfortunately I cannot share the file since it contains client data. The month column is a number, I was referring to the original date column. I went back to my data source and update the dates on some rows so I wouldn't have any duplicate rows to see if that would fix it but that didn't seem to fix the problem.
Ok. without something to work with it is difficult to assist. But let me try one more thing.
Download this PBIX file. It has some random data I put together for 2020. Notice how the chart is not sorted by month properly. I'm not sure how it is sorted. It isn't alphapbetical or by date. Do the following:
Ensuring the chart is sorting correctly.
It should now be sorted by the Month Name, which in turn is sorted by the numerical month number.
See if walking through that process helps. I don't know what else to do if I cannot have access to your PBIX file to more closely examine the issue.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWorks like a charm. Thank you so much!!!
@edhans, thanks for that. I was able to test your file and was able to play around with it. I don't have a date table which might be onbe of the reasons why I cannot sort my data. I was trying to sort the data based on the date column on the same table. I will try out creating a date table and mapping it to my data table to see if I can make that work. Thanks
Great. You do not have to have a date table, but it makes your life easier if you do, in many ways, and without one, Time Intelligence functions simply do not work.
That M code in my file is my standard Date table that I copy and paste in to all new files. I actually use this method to make it 100% dynamic so it is always covering all data in my file.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou have to have 1 value per sorting value. So if you are sorting by month, January would be 1, February would be 2, etc. By doing the Index, January may have gotten 1, and 13, and 27, etc.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIf you only want shown month name then it better than you come by date hierarchy drill. Else preferably have month year and month year sort in your table and use that.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
You do not have to sort by a date column, but you do have to provide a sortable column in your data set so Power BI knows how to sort. A date table is far and away the best practice here, but you can use any column to sort. See this article.
If you cannot get it to work, can you share your PBIX file via OneDrive or other method so someone can take a look at it? There is no workaround here. Sort By Columns is the thing. Period.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@jhern90 on top right corner of your visual, click three dots and make sure your have select correct column for sorting and sorting order is also correct.
Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |