Forum Discussion

UniHelp's avatar
UniHelp
New Member
4 years ago

Creating Line Chart with different filters per line?

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!

Need Help

2 Replies

  • danextian's avatar
    danextian
    Super User
    4 years ago

    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.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.