Forum Discussion
Helper IVMultiple Lines on One Chart
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 TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Solution SpecialistHi 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?
Helper IVHi 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 TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- e175429
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 Scalar Value. - SQLGene Training 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 TeamIf 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!
- e175429
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 CommunityConsidering 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 TeamIf 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!