cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
commodity_check
Helper II
Helper II

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

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.

commodity_check_1-1627052263202.png

 

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.

commodity_check_0-1627052210401.png

 

Kind regards,

 

 

 

 

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @commodity_check 

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.

4.png

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

1.png

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

1.png

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:

5.png

Select one line:

6.png

Select multiple lines:

7.png

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. 

View solution in original post

3 REPLIES 3
RicoZhou
Community Support
Community Support

Hi @commodity_check 

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.

4.png

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

1.png

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

1.png

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:

5.png

Select one line:

6.png

Select multiple lines:

7.png

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. 

View solution in original post

Hi @RicoZhou ,
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? 

Hi @commodity_check 

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

1.png

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. 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!