Forum Discussion
Line chart generate new lines based on filter
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?
- 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].
- 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.
- 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.
- 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.
- 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).
3 Replies
- 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].
- 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.
- 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.
- 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.
- 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 🙂
Hi,
You have not shared Table1 at all? Share the download link of the PBI file. Show the problem there.
