Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
nok
Helper II
Helper II

Line chart generate new lines based on filter

Hello!
I have a ref table in this format:
"Ref_Table"

IND_ID  Category
1LA
2LA
3LA
1HU
2KL
3KL
3ZS

 

And other table in this format:
"Table2"

IND_ID  Date       Value  
11/1/2024  76
11/2/202412
11/3/20243
21/1/202445
21/2/20246
31/1/20247

 

In my dashboard, I have a line chart that shows only two lines, the blue and red values ​​over the months (all this data comes from "Table1").

I want this line chart to also show a new line for each indicator based on the Category filter selected by the user. So I related "Table1" with "Ref_Table" through the Category column (cause "Table1" does not have IND_ID column, only Category column) and I also related "Ref_Table" with "Table2" through the IND_ID column. So how do I make the line chart show the number of new lines based on the category filter set by the user?


For example:
if the user selects the category "LA" in  the filter, then the line chart will show the blue, red lines and also three new lines (one for each corresponding IND_ID from "Table2")

If the user selects the category "ZS" in the filter, then the line chart shows the blue, red lines and one more line (IND_ID 3 line)


How can I do this? Is there a way to make the number of lines shown in my line chart variable according to the filter that the user uses in the dashboard?

1 ACCEPTED SOLUTION
Shravan133
Super User
Super User

  1. Set Up Relationships:

Ensure you have the correct relationships between your tables:

  • Ref_Table[IND_ID] should be related to Table2[IND_ID].
  • Table1[Category] (if it exists) should be related to Ref_Table[Category].
  1. Create a Measure for the Line Chart:

You'll need to create a measure that calculates the values for Table2, filtered by the selected Category from Ref_Table. Here's a sample DAX measure:

DynamicLineValue =

CALCULATE(

    SUM(Table2[Value]),

    TREATAS(

        VALUES(Ref_Table[IND_ID]),

        Table2[IND_ID]

    )

)

This measure will dynamically sum the Value column in Table2 based on the IND_ID values associated with the selected Category in Ref_Table.

  1. Configure the Line Chart:
  • Drag your Date column from Table2 to the Axis field in the line chart.
  • Drag the original measure(s) you were using for the blue and red lines to the Values field.
  • Drag the DynamicLineValue measure you just created to the Values field as well.
  1. Use a Category Filter:
  • Add a slicer or dropdown filter to your report using the Category column from Ref_Table.
  • When a user selects a category, the line chart will now show the original lines (blue and red) and additional lines for each IND_ID associated with the selected category.
  1. Result:
  • If the user selects "LA", the chart will show the original lines plus three new lines (for IND_ID 1, 2, and 3).
  • If the user selects "ZS", the chart will show the original lines plus one new line (for IND_ID 3).

 

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

You have not shared Table1 at all?  Share the download link of the PBI file.  Show the problem there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Shravan133
Super User
Super User

  1. Set Up Relationships:

Ensure you have the correct relationships between your tables:

  • Ref_Table[IND_ID] should be related to Table2[IND_ID].
  • Table1[Category] (if it exists) should be related to Ref_Table[Category].
  1. Create a Measure for the Line Chart:

You'll need to create a measure that calculates the values for Table2, filtered by the selected Category from Ref_Table. Here's a sample DAX measure:

DynamicLineValue =

CALCULATE(

    SUM(Table2[Value]),

    TREATAS(

        VALUES(Ref_Table[IND_ID]),

        Table2[IND_ID]

    )

)

This measure will dynamically sum the Value column in Table2 based on the IND_ID values associated with the selected Category in Ref_Table.

  1. Configure the Line Chart:
  • Drag your Date column from Table2 to the Axis field in the line chart.
  • Drag the original measure(s) you were using for the blue and red lines to the Values field.
  • Drag the DynamicLineValue measure you just created to the Values field as well.
  1. Use a Category Filter:
  • Add a slicer or dropdown filter to your report using the Category column from Ref_Table.
  • When a user selects a category, the line chart will now show the original lines (blue and red) and additional lines for each IND_ID associated with the selected category.
  1. Result:
  • If the user selects "LA", the chart will show the original lines plus three new lines (for IND_ID 1, 2, and 3).
  • If the user selects "ZS", the chart will show the original lines plus one new line (for IND_ID 3).

 

Amazing!!
Thank you very much 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.