Creating Line Chart with different filters per line?

[UniHelp]

Hi all, 

I am very new to BI and I've been tasked with creating a dashboard to replace some manually created charts for reporting we do. I need to work out how to create a line chart where the same measure is shown across three different groups, and these groups are identified by different fields. See a mock example of the type of excel chart I am trying to replicate below. 

In Power BI, the three fields for filtering are like this, colour coded:

So essentially with  X axis being Year and Y-axis being the measure (E.g. Pass rate), I then need each line to have a filter from a different field, so 'course code' = AccountingBach of one line, 'Course College' = College of Business for another, and so on. 

How do I apply different filters to each line on a line chart? Is it possible? 

I have the following restrictions due to institutional rules: 

-I cannot access the raw data or change the model, that is restricted to a specific team in the University. I can ask them to make changes for me, within reason. 

-I cannot download custom visual, it's against institutional policy. 

Thank you for your help, I really appreciate it!

[Ashish_Mathur]

Hi,

Please request your team for the following:

1. Unpivot all columns other than the Field column
2. Create a Date column in your data Table.  Could be the first month and day of every year
3. Create a Calendar Table with columns of Year, Month name and Month number
4. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table

Hope this helps.

[danextian]

Hi @UniHelp ,
Not being able to manipulate the raw data in Power Query is very limiting. What you can do now is create a measure  for each of the line in your line chart. You may follow this format:

my measure =
CALCULATE (
    [aggregation],
    FILTER ( DataTable, DataTable[Column] = "Something" )
)

Aggregation could be SUM (DataTable[Column]), COUNT(DataTable[Column]), etc.