I was working on a report recently that required a scatter plot of values to be displayed. The scatter plot is quite a useful visual in that it can show the distribution of values at-a-glance and make spotting outliers easier.
I was able to pull the data together quite easily and display the visual with the correct values. This should have been a visual that took no longer than 30 minutes to put together including some of the complex aggregations I needed to pull together to display the data the way I wanted.
I built the visual and was quite happy with the end result, but there was one thing that was bugging me. The data would not sort by Date. My first thought was to try to create a numerical sort sequence column and sort the date column by that, but this opened another can of worms – mainly I’m using a direct query data model and RANKX is not supported on calculated columns. This was really out of desperation, but I don’t think it would have worked anyway.
A quick Google search found that there were many other people who have the same issue. The issue being when you have a Scatter Plot with a legend, it doesn’t correctly sort the data by date. It’s a bug that has been around for several years now, but the Power BI dev team hasn’t resolved it yet.
Eventually I found a post that found a resolution to this madness. From the Formatting pane for the Scatter Plot visual, Expand the Y-Axis formatting and turn on the setting for Show blank values.
Once I turned this setting on, the data was nicely ordered by the Date field.
