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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
cmacdo
Microsoft Employee
Microsoft Employee

Allow user to select values to be shown?

I have a line graph right now that is showing the changes in several measures I am computing over time.  However, because I have 20 or so measures I want to be able to show the changes for, I was wondering if there was some way to have the viewer of the report select which lines they want to see at any given time.  I don't want to do a page wide filter because I only want the user selection to effect this visualization, and not the rest of the page.  Also, I have only really seen ways for the user to select values for a particular attribute, instead of just which attributes are being shown.  The tool tip on my line graph also seems to only go up to 5 attributes, which is unfortunate since I have a lot more then that, and basically then makes it so I will either need to have several separate graphs with up to 5 attributes each if there is not a user selection feature on which to actuall show.  Any thoughts on possible other solutions?

1 ACCEPTED SOLUTION

@cmacdo


@cmacdo wrote:

That actually looks like exactly what I need, but I'm going to need to play with it to get it to work for my needs and in PowerBI.  The user is probably only going to want to be able to view one line at a time anyways, so one slicer should be enough.  If you have any more detailed instructions that would be extremely helpful!  Thanks so much!


To view one line at a time, you can create a table with one column valuing measures names. And filter the measures names in a slicer. In the line chart, put the a measure similar to below.

MeasureShown =
IF (
    HASONEVALUE ( 'MeasureNameTable'[MeasureName] ),
    (
        SWITCH (
            LASTNONBLANK ( 'MeasureNameTable'[MeasureName], "blank" ),
            "Measure1", [Measure1],
            [Measure2]
        )
    ),
    [DefaultMeasure]
)

View solution in original post

9 REPLIES 9
MattAllington
Community Champion
Community Champion

add a slicer for the attribute you want to control to your page.  Then click on your new slicer and select "format\edit interations".  from here you can conrol what objects your new slicer impacts.  Just turn off the sliciing on every other visualisation.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

I think you misunderstood my question.  I understand that a slicer allows you to filter for particular values for an attribute.  However I want the user to be able to select only particular attributes/measures to be shown.  For example, lets say I have a line chart that displays three measures over time, let's call these each A,B,C.  I want the user to be able to deselect B so that the chart now only has two lines on it, one for A, and one for B.  This has nothing to do with filtering on A or C, just allowing the user to remove the line for measure B from the chart altogether for their viewing purposes.

 

In my case, I have 20 or so of these measures I am trying to show, and it will be impossible for the user to see a specific value for 15 of them at a given date because the tooltip will only show the first 5.  Hence why I want the user to be able to select which ones they would like to see at any given time.  It might be impossible in the current set up, but if there is any custom visualization that allows this I would be all for trying that as well.

You can't add or remove measures with a slicer. However if you have a fixed number of measures in a chart, you can swap "which" measures are displayed. Read this article I wrote some time ago. 

http://www.powerpivotpro.com/2014/10/5-interactive-chart-techniques-come-together/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

That actually looks like exactly what I need, but I'm going to need to play with it to get it to work for my needs and in PowerBI.  The user is probably only going to want to be able to view one line at a time anyways, so one slicer should be enough.  If you have any more detailed instructions that would be extremely helpful!  Thanks so much!

@cmacdo


@cmacdo wrote:

That actually looks like exactly what I need, but I'm going to need to play with it to get it to work for my needs and in PowerBI.  The user is probably only going to want to be able to view one line at a time anyways, so one slicer should be enough.  If you have any more detailed instructions that would be extremely helpful!  Thanks so much!


To view one line at a time, you can create a table with one column valuing measures names. And filter the measures names in a slicer. In the line chart, put the a measure similar to below.

MeasureShown =
IF (
    HASONEVALUE ( 'MeasureNameTable'[MeasureName] ),
    (
        SWITCH (
            LASTNONBLANK ( 'MeasureNameTable'[MeasureName], "blank" ),
            "Measure1", [Measure1],
            [Measure2]
        )
    ),
    [DefaultMeasure]
)
Anonymous
Not applicable

This looks exactly what i need, do you have an exemple ou a tutorial of how you applied this?

This actually works extremely well, thank you so much!  Just one more question, is there anyway to have this work with the original data being from direct query?  I can't seem to find a way to create the MeasureNameTable without having to switch to import.  I guess I can just set up data refresh, but seems like it should be unnecessary since my original data is from an Azure SQL table.

@cmacdo

Up to my knowledge, no way in direct query to create a table. Maybe import a MeasureNameTable from SQL Azure DB?

The workbook I used is attached to the blog post. You can download it to see what I did 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.