Custom Sort on Line Charts

[bselby]

Good afternoon,

I have a question about creating a custom sort on a line chart, which I thought would be fairly simple but is actually proving very difficult.

The scenario I'm having issues with is creating a month-based x-axis, running from Dec to Nov, to compare two years (i.e. Dec 16 - Nov 18).

So far, I've gotten it to work fine provided there is data for every month. The problem is, as soon as granular filters are introduced, any month with no data can no longer be sorted properly.

The setup is the Month-only element of the date hierarchy on the x axis, with a field containing the sort order (Dec = 1, Jan = 2 etc) on the tooltip, and then the visual sorted by the tooltip.

Here is an example of a customer filter with data for every month, where it works fine:

 

Here is an example of a customer filter with data for only some months, along with the "show data" result:

I can see the missing months are there (I have used the sum value + 0 method mentioned in other threads for getting a continual line where data doesn't exist), but because there is no actual data, the tooltip doesn't apply and they are simply sorted to the start as nulls (but somehow in the standard annual order still).

If I use an alphanumeric x-axis to force the sort instead of via the tooltip, I can't get the continual line or missing months to show:

Does anyone know a solution? I've seen some suggestions in other threads regarding adding in additional date tables, but I'm using a Direct Query connection to SAP HANA so a lot of those features are disabled in Power BI.

[v-yulgu-msft · Accepted solution]

Hi @bselby,

Did you have a date dimention table in this scenario? If not, please create one via CALENDAR function. Establish a relationship between date dimention table and source data table. Rather than add [Month] from source data table, please drag it from date dimention table onto X-axis instead. Also, the custom sort should be applied to date dimention table.

Regards,

Yuliana Gu

[bselby]

Hi Yuliana,

Thank you for your response.

In the above example I didn't have a seperate Date table, as I couldn't create a new table in Direct Query mode for a SAP HANA Database. However I have been advised in a separate thread that if I swap it to a "relational source" from the options, it will enable that sort of functionality.

Having done that first, I have followed your suggestion and created a Date table via the CALENDAR function, added my custom sort to that table and used them both on the visual. I am happy to say that it now works perfectly:

Thank you very much for your help!