Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ipelly
New Member

Axis sorting not working when "Show Items with No Data" checked

I have a table with some null data, and am plotting it by year/month. Even though the X axis is sorted by year / month (so 201801, 201802, etc), all the months/years with no data are coming first, even though they're later than the months/years with data. Pic attached.

 

badorder.png

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @ipelly 

According to your description, I can understand clearly what you want to get, I think you can achieve this using another calculate column to sort the [Year month] column, you can try my steps:

  1. Create two columns with the same DAX formula but different names in the data table like this:
Yearmonth = YEAR([Date])&FORMAT([Date],"mm")
Sort column = YEAR([Date])&FORMAT([Date],"mm")

Then change them to different data type, one “Whole number” and one “Text”, like this:

v-robertq-msft_0-1616493097768.png

v-robertq-msft_1-1616493097777.png

 

  1. Sort the column [Yearmonth] like this:

v-robertq-msft_2-1616493097782.png

 

  1. Go to the line chart, sort by [Yearmonth]

v-robertq-msft_3-1616493097794.png

 

And you can get what you want

You can download my test pbix file here

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
Community Support

Hi, @ipelly 

According to your description, I can understand clearly what you want to get, I think you can achieve this using another calculate column to sort the [Year month] column, you can try my steps:

  1. Create two columns with the same DAX formula but different names in the data table like this:
Yearmonth = YEAR([Date])&FORMAT([Date],"mm")
Sort column = YEAR([Date])&FORMAT([Date],"mm")

Then change them to different data type, one “Whole number” and one “Text”, like this:

v-robertq-msft_0-1616493097768.png

v-robertq-msft_1-1616493097777.png

 

  1. Sort the column [Yearmonth] like this:

v-robertq-msft_2-1616493097782.png

 

  1. Go to the line chart, sort by [Yearmonth]

v-robertq-msft_3-1616493097794.png

 

And you can get what you want

You can download my test pbix file here

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@ipelly , just check that you have sorted on month year column using three dots

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.