Line chart generate new lines based on filter

[nok]

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

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

And other table in this format:
"Table2"

IND_ID	Date	Value
1	1/1/2024	76
1	1/2/2024	12
1	1/3/2024	3
2	1/1/2024	45
2	1/2/2024	6
3	1/1/2024	7

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?

[Ashish_Mathur]

Hi,

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

[Shravan133 · Accepted solution]

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

[nok]

Amazing!!
Thank you very much 🙂