How to build a Slicer for the Lines on a 'Line & Clustered Column' Chart?

[JohnJoe123]

Hi Everyone,

Here is a Link to my Power BI File via Google Drive:

https://drive.google.com/file/d/1F13hFYYGtAjgbcwYLvx-97YfM8MXfDDd/view?usp=drive_link

So, I have built a Line & Clustered Column Chart with the Data from my Power BI Query.

An image of said Chart is shown below.

I currently have 3 Lines on this Graph - Called Line A, Line B and Line C.

(Red, Green & Pink Lines Respectively)

My Question is: 

Is is Possible to Build a Slicer which can Toggle these Lines On & Off on the Graph?

I'd like Something like the Week Number Slicer that I currently have in my Power BI File.

So the final Result would be something like this: (I made the below image in Power Point)

[v-huijiey-msft · Accepted solution]

Hi @JohnJoe123 ,

Yes, you can do it.

Firstly, create a table contain all lines’ name:

Lines
Line A
Line B
Line C

I noticed that the aggregation method of your lines is Average. Therefore, create a measure to store three lines for calling in the newly created table:

_Line A = AVERAGE('D - ISP'[Line A])

_Line B = AVERAGE('D - ISP'[Line B])

_Line C = AVERAGE('D - ISP'[Line C])

Drag the Lines field of the new table into the Slicer:

Create a measure:

Measure = SWITCH(TRUE(),
SELECTEDVALUE(Lines[Lines]) = "line A", 'D - ISP'[_Line A],
SELECTEDVALUE(Lines[Lines]) = "line B", 'D - ISP'[_Line B],
SELECTEDVALUE(Lines[Lines]) = "line C", 'D - ISP'[_Line C]
)

Place measure on the Line y-axis of the chart, and the final page effect is as shown below:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[v-huijiey-msft · Accepted solution]

Hi @JohnJoe123 ,

Yes, you can do it.

Firstly, create a table contain all lines’ name:

Lines
Line A
Line B
Line C

I noticed that the aggregation method of your lines is Average. Therefore, create a measure to store three lines for calling in the newly created table:

_Line A = AVERAGE('D - ISP'[Line A])

_Line B = AVERAGE('D - ISP'[Line B])

_Line C = AVERAGE('D - ISP'[Line C])

Drag the Lines field of the new table into the Slicer:

Create a measure:

Measure = SWITCH(TRUE(),
SELECTEDVALUE(Lines[Lines]) = "line A", 'D - ISP'[_Line A],
SELECTEDVALUE(Lines[Lines]) = "line B", 'D - ISP'[_Line B],
SELECTEDVALUE(Lines[Lines]) = "line C", 'D - ISP'[_Line C]
)

Place measure on the Line y-axis of the chart, and the final page effect is as shown below:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[JohnJoe123]

Hi,

Sorry for just responding now!

Thank you so much for this!

Maybe I seem pedantic, but there is one small issue with your Solution.

When I click on just one Line in the newly created Slicer - it displays the selected Line no problem.

But if I select multiple Lines - it does not show any of them.

I'd like the Slicer to work such that I could select all 3 Lines at once, and that they would then all be visible on the graph at once. (And also I'd like it that each Line would still have a different Colour - like the Red, Green and Pink in my Original Image)

Is this Possible?

[v-huijiey-msft]

Hi @JohnJoe123 ,

Modify these three measures:

_Line A = IF("Line A"IN VALUES(Lines[Lines]),AVERAGE('D - ISP'[Line A]))

_Line B = IF("Line B"IN VALUES(Lines[Lines]),AVERAGE('D - ISP'[Line B]))

_Line C = IF("Line C"IN VALUES(Lines[Lines]),AVERAGE('D - ISP'[Line C]))

Put these three measures into the Line y axis, and the page effect is as follows:

The pbix file has been attached, if you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[JohnJoe123]

Thank you!

Just one more thing:

Is it possible to preserve the graph's current functionality - while also adding another feature?

This other feature being:

Is it possible to make all 3 Lines Disappear / Invisible - when NONE of them are Selected in the Relevant Slicer?

[v-huijiey-msft · Accepted solution]

Hi @JohnJoe123 ,

Please try:

Modify the measure syntax as follows:

_Line A = IF(ISFILTERED('Lines'[Lines]),IF("Line A"IN VALUES(Lines[Lines]),AVERAGE('D - ISP'[Line A])),BLANK())

_Line B = IF(ISFILTERED('Lines'[Lines]),IF("Line B"IN VALUES(Lines[Lines]),AVERAGE('D - ISP'[Line B])),BLANK())

_Line C = IF(ISFILTERED('Lines'[Lines]),IF("Line C"IN VALUES(Lines[Lines]),AVERAGE('D - ISP'[Line C])),BLANK())

Other operations remain unchanged, and the page effect is as shown below:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[JohnJoe123]

Thank you so much for this!

It is working perfectly for me now! 🙂