topic Re: Multiple Lines on One Chart in DAX Commands and Tips

Multiple Lines on One Chart

e175429 — Tue, 13 Aug 2024 19:03:07 GMT

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.

2024.xlsx

2023.xlsx

What am I doing incorrectly?

Regards

Re: Multiple Lines on One Chart

arava — Tue, 13 Aug 2024 19:17:57 GMT

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.

Re: Multiple Lines on One Chart

e175429 — Tue, 13 Aug 2024 19:44:23 GMT

Hello. So, I actually get the same result using the columns LIKELY from the attached files.

2023:

2024:

But when I put them together:

Re: Multiple Lines on One Chart

arava — Tue, 13 Aug 2024 19:49:04 GMT

The attached files are not publicly available and the suggestion was to change the visual itself, not change the inputs

Re: Multiple Lines on One Chart

ahadkarimi — Tue, 13 Aug 2024 20:20:22 GMT

e175429 I get this error when trying to download your Excel file:

Re: Multiple Lines on One Chart

e175429 — Wed, 14 Aug 2024 03:50:08 GMT

Re: Multiple Lines on One Chart

e175429 — Wed, 14 Aug 2024 03:52:28 GMT

Hi ahadkarimi,

Please try again.

2023

https://1drv.ms/x/s!Aop4ARcj8m8hgQTArIQ07rOUmCyd

2024

https://1drv.ms/x/s!Aop4ARcj8m8hgQeMN1GH0X29SBMJ

Thank you kindly

Re: Multiple Lines on One Chart

ahadkarimi — Wed, 14 Aug 2024 04:11:27 GMT

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?

Re: Multiple Lines on One Chart

e175429 — Wed, 14 Aug 2024 04:37:54 GMT

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).

Re: Multiple Lines on One Chart

Anonymous — Wed, 14 Aug 2024 08:56:58 GMT

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.

Re: Multiple Lines on One Chart

e175429 — Wed, 14 Aug 2024 21:45:34 GMT

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."

Re: Multiple Lines on One Chart

Anonymous — Thu, 15 Aug 2024 06:24:59 GMT

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 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!

Re: Multiple Lines on One Chart

e175429 — Thu, 15 Aug 2024 15:03:07 GMT

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.

Re: Multiple Lines on One Chart

e175429 — Thu, 15 Aug 2024 21:05:52 GMT

Anonymous

One more question.

I used your method on the CON column. How do I get the x-axis sorted numerically?

Re: Multiple Lines on One Chart

Anonymous — Fri, 16 Aug 2024 06:01:43 GMT

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

Re: Multiple Lines on One Chart

e175429 — Mon, 19 Aug 2024 16:20:21 GMT

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: