How to filter lines in a "Line and stacked column chart"

[Anonymous]

Hi all,

I have a Line and stacked column chart.

For the lines, I need the users of the report to easily let them select whether they want to see 2 lines, or 4, or 6 etc.

I wanted to do this using a bookmark but there are some issues with this:

If I use unpivoted data the lines aggregate, making it impossible to show two lines at once (especially since the "Line and stacked column chart" has no legend field like the regular line chart).

If I use pivoted data I get all lines in the bar chart but I am unable to create a filter/bookmark to make selections of which lines I want to see.

Unpivoted data: making it impossible to select two or more lines. The lines get aggregated.

Pivoted data: all the lines are in there but it is not possible to make a filter/bookmark so that I can click a button and for example 2 certain lines show.

Kind regards,

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

You can build measures for your line value instead of adding columns into line field in your visual.

Here I build a sample. My data model looks like your Pivote model. Unpivote model is not suitable, due to the line will show only one sum line.

Build a Line and stacked column chart as below. We couldn't select column header directly.

Firsly, build a new table with all line names. Then build a slicer by this column.

Build measures to replace columns in line values.

M_Line1 = 
VAR _selection = VALUES('Line Name'[Line Name])
VAR _Result = IF("Line1"in _selection,SUM('Table'[Line1]),BLANK())
RETURN
_Result

M_Line2 = 
VAR _selection = VALUES('Line Name'[Line Name])
VAR _Result = IF("Line2"in _selection,SUM('Table'[Line2]),BLANK())
RETURN
_Result

M_Line3 = 
VAR _selection = VALUES('Line Name'[Line Name])
VAR _Result = IF("Line3"in _selection,SUM('Table'[Line3]),BLANK())
RETURN
_Result

Result is as below.

By default no selection:

Select one line:

Select multiple lines:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @v-rzhou-msft ,
This is amazing thanks a lot! It works great!

But I do not understand exactly why it works would it be possibel for you to ellaborate a bit? 

[v-rzhou-msft]

Hi @Anonymous 

As I explained as above, in power bi we couldn't select column headers directly if your data model looks like as below.

Many times we will unpivote the table and get column names(Line1/Line2/Line3) into a column. Then we can filter them. However this kind of model will cause Line and stacked column chart show only one sum line. So we can't unpivote the data model, the original data model is better.

Build a unrelated table with all line names for slicer. Then we build dax code for line values. 

Note: Column can not be dynamic, so we need to build measures.

EX:

M_Line1 = 
VAR _selection = VALUES('Line Name'[Line Name])
VAR _Result = IF("Line1"in _selection,SUM('Table'[Line1]),BLANK())
RETURN
_Result

VAR Function is Hypothetical function, here _selections is a list with all line names we select in slicer. Then we use if function to get values if Line1 in _selection list and get blank if Line1 is not in _selection list. Finally return the result.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.