Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hey Everyone,
I am trying to put 2 lines on one chart from 2 different tables to compare data over 2 years. Seperately, they are:
and
When I attempt to put them on one chart I get:
As you can see, the data points are all wrong.
Below is the data I am using.
What am I doing incorrectly?
Regards
Solved! Go to Solution.
Thanks for the reply from arava and ahadkarimi.
Hi @e175429 ,
When using the 2024 PLEA field as the x-axis, it seems to appear as you would expect, but on closer comparison, the data is incorrect.
I will provide an alternative method and here are the steps to do it:
1.Create a new table, this table contains unique PLEA column values extracted from 2023 and 2024 (Sheet1):
Newtable =
SUMMARIZE (
UNION (
SELECTCOLUMNS ( '2023', "PLEA", '2023'[PLEA] ),
SELECTCOLUMNS ( '2024(Sheet1)', "PLEA", '2024(Sheet1)'[PLEA] )
),
[PLEA]
)
2.The relationship between the three tables is created as shown below:
3.Using the PLEA of the newly created table as the x-axis, you can get a line graph with the correct data:
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Zhu
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
.
Hi @ahadkarimi,
Please try again.
2023
https://1drv.ms/x/s!Aop4ARcj8m8hgQTArIQ07rOUmCyd
2024
https://1drv.ms/x/s!Aop4ARcj8m8hgQeMN1GH0X29SBMJ
Thank you kindly
Hi @e175429, thank you for sharing the files. When I connected the two table IDs in the Model view, it showed the lines perfectly in one chart. Do you not want a relationship between the two tables?
Hi @ahadkarimi
I forgot to mention that I also created the relationship by ID.
The problem occurs when I go to line chart by columns PLEA from 2023 and 2024, it doesn't come out right (re: the 3 pictures from my first post).
Thanks for the reply from arava and ahadkarimi.
Hi @e175429 ,
When using the 2024 PLEA field as the x-axis, it seems to appear as you would expect, but on closer comparison, the data is incorrect.
I will provide an alternative method and here are the steps to do it:
1.Create a new table, this table contains unique PLEA column values extracted from 2023 and 2024 (Sheet1):
Newtable =
SUMMARIZE (
UNION (
SELECTCOLUMNS ( '2023', "PLEA", '2023'[PLEA] ),
SELECTCOLUMNS ( '2024(Sheet1)', "PLEA", '2024(Sheet1)'[PLEA] )
),
[PLEA]
)
2.The relationship between the three tables is created as shown below:
3.Using the PLEA of the newly created table as the x-axis, you can get a line graph with the correct data:
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Zhu
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
One more question.
I used your method on the CON column. How do I get the x-axis sorted numerically?
Hi @e175429 ,
My answer to your first question is that in Power BI visual objects will calculate the corresponding values based on the fields we provide for the x-axis and the relationships we define, and in Power BI if the category column of one of your tables does not fully contain the category field of the other table, using the category field of one of the tables to create the x-axis will result in us displaying the line graphs incorrectly in a line graph. correctly. So we need to create a dim table that contains all the categories from both tables and use it as the x-axis to establish the correct relationship with the other two tables.
Your second question how do you sort the Con columns, the numeric format is automatically sorted when you use the line chart. How you want to sort the other formats you can refer to the link below:Solved: Putting stacked column chart in order - Microsoft Fabric Community
Considering that you need to compare different columns, I will give you an alternative method here.
1.Create a new year column for 2023 and 2024 in Power Query Editor.
2.Append operation:
3.You can now use the fields inside append1 table directly, and using Year as the legend allows you to visually compare two years of data.
Best Regards,
Zhu
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
On the y-axis, when I want to change to "Show value as percent of grand total", why does that not show the correct percentages?
These are the correct numbers:
But these are not the correct percetages:
Hi @Anonymous
When I try it I get the following error:
"The expression refers to multiple columns. Multiple columns cannot be conevrted to a scalar value."
Hi @e175429,
I'm sorry about this. I created a new table, are you doing the same as me? You can click on the link below to see where the error is occurring:DAX Error: The Expression Refers to Multiple Columns. Multiple Columns Cannot Be Converted to a Scal... and if the problem persists, please give me a detailed description and I will be able to better solve your problem.
Best Regards,
Zhu
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hello,
Sorry, the mistake was mine I was doing it wrong.
Thank you very much, your suggestion gave me what I was ooking for.
So, moving forward, as time goes on, i'll have more years to compare. This is what I'll have to o each time for each column from different tables I want to compare? I don't understand why BI didn't just map the data points initially, instead of having to create a whole new table.
The attached files are not publicly available and the suggestion was to change the visual itself, not change the inputs
Hello! Line charts are meant to be used for quantitative, ordered values. I might suggest a clustered column chart to tell your story more intuitively.
Hello. So, I actually get the same result using the columns LIKELY from the attached files.
2023:
2024:
But when I put them together:
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |