cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bselby Frequent Visitor
Frequent Visitor

Custom Sort on Line Charts

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:

 

 Line Chart Sort All Data.JPG

 

 

 

 

 

 

 

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

 

Line Chart Sort Missing Data.JPG

 

 

 

 

 

 

 

 

 

 

Line Chart Drill Data.JPG

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

Line Chart Alphanumeric Sort.JPG

 

 

 

 

 

 

 

 

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: Custom Sort on Line Charts

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

Community Support Team _ Yuliana Gu
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-yulgu-msft Super Contributor
Super Contributor

Re: Custom Sort on Line Charts

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

Community Support Team _ Yuliana Gu
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

bselby Frequent Visitor
Frequent Visitor

Re: Custom Sort on Line Charts

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:

 

Line Chart Working.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thank you very much for your help!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 1,358 guests
Please welcome our newest community members: