Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ashamsuzzoha
Advocate II
Advocate II

How can I toggle line/columns on line charts?

I have the following line and column chart and the user is requesting the ability to toggle columns/lines on and off. How do I do that? The columns on all on the same table. I made a disconnected table of the column names but I'm not sure where to go from here.Screenshot 2021-03-03 111932.png

 

2 ACCEPTED SOLUTIONS

Hey @ashamsuzzoha , 

you can achieve this, by creating a table that represents the measure name, this table fills the slicer that allows users to select what they want to see on the visual. but is otherwise unrelated to your data model.
All the measures have to be assigned to the visual in advance 🙂 as it's still valid what I said above, that you can't programmatically assign a measure / column to a visual.

Assuming that there is a measure called Measure 1, you have to do 4 things

  • add the value "Measure A" to the visual
  • add a row to the unrelated table that is populating the "Measure slicer"
  • create a slicer based on the unrelated table
  • adjust your measure in a way that it reflects the selection inside the slicer (see the DAX below)

As the measures might be used outside of the context of the visual we are currently talking about, the DAX statement that toggles the "visibility" has to be a little more generic.

If I implement something like this, I consider it useful to remember that the selection of all items from a slicer means basically the same as no item is selected in regards to the rows that will be filtered, but can mean a great difference to use this information for toggling the visibility of a measure.

Another aspect to keep in mind, is the fact that visibilty of a measure means leveraging the fact that visuals (most of them) honor the value BLANK() in one way or the other.
In the sample report (see link below), the measure name will always appear in the legend of the line and stacked column chart no matter if a measure returns a value.
Here is the DAX statement for one measure:

 

 

Measure 1 = 
var measureTableIsFiltered = ISFILTERED( 'unrelated table' )
var filteredMeasure = VALUES( 'unrelated table'[Measure] )
return
IF( NOT( measureTableIsFiltered ) || "Measure 1" in filteredMeasure
    ,
    // the below DAX computes the actual measure - here a simple constant value
    1
    // the above DAX computes the actual measure - here a simple constant value
    , BLANK()
)

 

 


Here you will find a sample pbix file
A screenshot of what's inside the pbix (the card visuals are just used for decoration and testing):
image.png

Hopefully, this provides what you are looking for, if not create a pbix that contains sample data but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data share the xlsx as well.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

V-pazhen-msft
Community Support
Community Support

@ashamsuzzoha 
You can achieve this only for measures, you may create a disconnected slicer table including measure using IF(Contains()....). See attached example pbix.

V-pazhen-msft_0-1615184888132.png

 


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
V-pazhen-msft
Community Support
Community Support

@ashamsuzzoha 
You can achieve this only for measures, you may create a disconnected slicer table including measure using IF(Contains()....). See attached example pbix.

V-pazhen-msft_0-1615184888132.png

 


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

TomMartens
Super User
Super User

Hey @ashamsuzzoha ,

 

you can achieve what you want by using bookmarks, but you have to create 2 versions of the visual and use a button to toggle between theses versions. Here you will find an articel that explains bookmarks: Overview of bookmarks in Power BI service reports - Power BI | Microsoft Docs

 

This is necessary as there is no feature that allows to programmatically change the content of a visual. 

 

Hopefully, this provides some ideas on how to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Extremely frustrating answer, I would need a chart for every permutation of the line and chart. So 7! (=5040) charts??


@TomMartens wrote:

This is necessary as there is no feature that allows to programmatically change the content of a visual. 


Thanks Microsoft.

Pragati11
Super User
Super User

Hi @ashamsuzzoha ,

 

Do you require something like - Toggle between 2 charts: 1 column chart and 2nd Line chart representing same information?

 

If yes then you can use bookmarks to do create these toggles in power BI. 

I wrote a blog sometime back on how you can toggle between 2 charts using bookmarks:

https://community.powerbi.com/t5/Community-Blog/Toggle-Between-Charts-in-Power-BI/ba-p/1345014

 

The only different thing you need to do is create one column chart and one Line chart using your information and then follow the above blog to toggle between them using buttons/bookmarks.

 

Let me know if this helps.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

No, user wants to ability to select any number of the columns/series with a check box.

HI @ashamsuzzoha ,

 

Please detail your requirement with some sample data and screenshots.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Something like this but for table columns, and being to select more than one table column at a time.
Change the Column or Measure Value in a Power BI Visual by Selection of the Slicer: Parameter Table ...

Hey @ashamsuzzoha , 

you can achieve this, by creating a table that represents the measure name, this table fills the slicer that allows users to select what they want to see on the visual. but is otherwise unrelated to your data model.
All the measures have to be assigned to the visual in advance 🙂 as it's still valid what I said above, that you can't programmatically assign a measure / column to a visual.

Assuming that there is a measure called Measure 1, you have to do 4 things

  • add the value "Measure A" to the visual
  • add a row to the unrelated table that is populating the "Measure slicer"
  • create a slicer based on the unrelated table
  • adjust your measure in a way that it reflects the selection inside the slicer (see the DAX below)

As the measures might be used outside of the context of the visual we are currently talking about, the DAX statement that toggles the "visibility" has to be a little more generic.

If I implement something like this, I consider it useful to remember that the selection of all items from a slicer means basically the same as no item is selected in regards to the rows that will be filtered, but can mean a great difference to use this information for toggling the visibility of a measure.

Another aspect to keep in mind, is the fact that visibilty of a measure means leveraging the fact that visuals (most of them) honor the value BLANK() in one way or the other.
In the sample report (see link below), the measure name will always appear in the legend of the line and stacked column chart no matter if a measure returns a value.
Here is the DAX statement for one measure:

 

 

Measure 1 = 
var measureTableIsFiltered = ISFILTERED( 'unrelated table' )
var filteredMeasure = VALUES( 'unrelated table'[Measure] )
return
IF( NOT( measureTableIsFiltered ) || "Measure 1" in filteredMeasure
    ,
    // the below DAX computes the actual measure - here a simple constant value
    1
    // the above DAX computes the actual measure - here a simple constant value
    , BLANK()
)

 

 


Here you will find a sample pbix file
A screenshot of what's inside the pbix (the card visuals are just used for decoration and testing):
image.png

Hopefully, this provides what you are looking for, if not create a pbix that contains sample data but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data share the xlsx as well.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors