Multi-line line graph with multiple columns for the x axis

[TrisBask]

Hi all,

Quite new to Power BI. I am trying to plot some student results data (education setting) into a multi-line graph.

This is what my data looks like, it represents pass rates at different reporting periods (every 6 months, seperated into semester 1 and semester 2 for each year).

Year Level	Subject Name	Faculty	2024-Sem1	2023-Sem2	2023-Sem1	2022-Sem2	2022-sem1
1	English	Primary	79	76	81	68	76
1	Maths	Primary	81	78	78	76	75
2	English	Primary	88	87	85	84	82
2	Maths	Primary	79	79	78	81	82
7	English	English	88	87	88	87	85
7	Maths	Maths	75	75	77	81	83
8	English	English	80	80	81	80	79
8	Maths	Maths	65	72	77	79	81
10	Biology	Maths	65	79	88	79	80

Ideally, I would like a line for each row in the table (subject/year level combination), plotting the different semester data points. This will show me trends for each year level/subject. When I have tried, I can't seem to get it to display what I am after.

I have tried putting each of my data columns into the X-axis, and the subject in the X-Axis, but that defaults to a count of the subjects which is not what I want.

When reversing this, I get a count of the semester results instead of just displaying their values.

I found a reference to Unpivot in my googling, but that didn't seem to help.

Any help is appreciated.

Thanks.

[Anonymous · Accepted solution]

Hi @TrisBask ,
Thanks for @amitchandak  reply.
Based on your description, you want to create a line graph with multiple line segments, and those segments need to contain every piece of data. Since you want to show the trend of the data at different stages, you need to use the Year-Period field as the X-axis, so you need to turn these field names into values in the columns, where Unpivot is used.In Power BI line charts, we can only show categories in one field. So, we also need to use the Small Multiples field. Here are the detailed steps:
First, we need to merge columns in power query, hold Ctrl to select the Subject Name and Faculty fields

After creating the merged column, hold Ctrl to select all Year-Period fileds and unpivot them

Close and apply and then you can create a line cahrt, you can slide the right slider to see the line graphs for different Subject Name-Faculty

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@TrisBask , In power query:  Unpivot all year-semester columns, and then split the new column with year-semester using split by delimiter into 2 columns. The format that you will get now should work

Power BI: Power Query Functions one must know: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=36407s