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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RossRobinson
Frequent Visitor

Power BI Line Chart visualisation with two columns in the X axis

Is it possible to display two columns in a Line Chart visulaisation?

To implement this in Excel is pretty straight forward (as shown below) so seems like it should be available in Power BI.

 

RossRobinson_0-1600920685832.png

The following configuration only shows the month number and not the year in the output. I have substituted this for the Year and Month in the date hierarchy and it is the same result. I can use a YYYYMM column but this is ugly. 

 

RossRobinson_0-1600921444278.png

It seems implausable to me that a long time Excel feature is not available in Power BI, am I missing something or would a different visualisation be better?

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@RossRobinson , Make sure both Year and Month number. I was able to get similar stuff with this setting. But month was text and sorted on month no

 

Concat 1.pngConcat2.png

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

Hey @RossRobinson ,

 

it's recommended not to say mandatory to use a dedicated date or calendar table inside your data model. This table then is related to the corresponding columns of the fact tables. Then the "month number" column can be used to create a chart like this:

TomMartens_0-1600922239363.png

This article covers almost everything about date related calculations in Power BI: https://www.daxpatterns.com/time-patterns/

 

This site provides a great introduction into the world of data modeling in Power BI: https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/

 

Hopefully, this provides what you are looking for, at least some ideas on how to tackle your challenge.

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom, thank you, I will look into that. At the moment I am just experimenting so it will be ideal to compare how things worked before and after I get to the maturity level whereby I create the dedicated date table.

amitchandak
Super User
Super User

@RossRobinson , Make sure both Year and Month number. I was able to get similar stuff with this setting. But month was text and sorted on month no

 

Concat 1.pngConcat2.png

Thank you Amit.

 

I got it to work based on your feedback and following this guys link (https://www.youtube.com/watch?v=MAdAa1t9FSY).

 

It took quite a bit of fiddling but it appears the following are key. Use the drill-down and turn the 'Concatenate labels' to 'Off' and dont use the 'Month number', this didnt work for me but when I replaced it with the 'Month name' (which was sorted by Month number) it did work out. I was so reluctant to experiment with using the 'Month name' instead of the 'Month number' because I couldnt see why this would make a difference!

 

One interesting observation, is if someone follows the URL above it uses a 'Stacked column chart' and the year and month from the date hierrachy. This worked for me in but when switching to the 'Line' chart the appealing X axis layout was replaced. This could be resolved by changing the X axis type from 'Continuos' to 'Categorical'. However after making the change there was no dialogue available to change it back!

 

I cant say it is the most intuative way of working but I suppose you only need to learn those steps once.

 

I wont post a screenshot of the resuly because it doesnt really differ from yours below.

 

Once again thank you for your assistance.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors