Forum Discussion
Filter to line chart?
Hi!
I need a line chart that only shows filtered data. User could use a filter and select the data they want to be seen in the line chart.
Here is my data (excel):
Here is the line chart:
Here is the filter - how should I build it?
Many thanks!
Tuomo, Finland
Hi nTuomo ,
I suggest you to create a Dim column header table to help calculation.
Dim data column header = DATATABLE( "data column",STRING, "Order",INTEGER, { {"data 1",1}, {"data 2",2}, {"data 3",3}, {"data 4",4}, {"data 5",5} } )Measure:
Measure = VAR _data1 = SUM('Table'[data 1]) VAR _data2 = SUM('Table'[data 2]) VAR _data3 = SUM('Table'[data 3]) VAR _data4 = SUM('Table'[data 4]) VAR _data5 = SUM('Table'[data 5]) RETURN SWITCH(SELECTEDVALUE('Dim data column header'[Order]),1,_data1,2,_data2,3,_data3,4,_data4,5,_data5)Result is as below.
Or you can Unpivot other columns by [year] in Power Query Editor to do some transformation on your table.
Then it will be easier to achieve your goal, you can add [Value] in Y axis and [Attribute] in Legend.
For reference: Unpivot columns - Power Query | Microsoft Learn
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hello nTuomo ,
As per your stated question, In Visualization tab we can use slicer visual to get the functionality you are looking for.Use this as a visual for your report buliding.
By following above steps you can get our fliter option which you are looking for.
For addition help:Anonymous Thank you for the reply!
But how could I get the filter to work so that user can select, which data is visible on line chart?
Selection should be between Data 1 / Data 2 / Data 3 ... etc
Not the way it is now (2 / 8 / 9 etc...) :
Hi nTuomo ,
I suggest you to create a Dim column header table to help calculation.
Dim data column header = DATATABLE( "data column",STRING, "Order",INTEGER, { {"data 1",1}, {"data 2",2}, {"data 3",3}, {"data 4",4}, {"data 5",5} } )Measure:
Measure = VAR _data1 = SUM('Table'[data 1]) VAR _data2 = SUM('Table'[data 2]) VAR _data3 = SUM('Table'[data 3]) VAR _data4 = SUM('Table'[data 4]) VAR _data5 = SUM('Table'[data 5]) RETURN SWITCH(SELECTEDVALUE('Dim data column header'[Order]),1,_data1,2,_data2,3,_data3,4,_data4,5,_data5)Result is as below.
Or you can Unpivot other columns by [year] in Power Query Editor to do some transformation on your table.
Then it will be easier to achieve your goal, you can add [Value] in Y axis and [Attribute] in Legend.
For reference: Unpivot columns - Power Query | Microsoft Learn
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
