Show values in a chart only when a slicer is selected

Quick one today. This question came up recently: “I only want to show values in a chart when someone selected a slicer”. Now this is actually not that hard, so here is the trick (and a little bonus too).

First I created a page I wanted it including the slicer:

Now the next thing I want is that the values of the visual only show when I select a country. First thing I do is write a measure to only return the value of the measure when a country is selected through the slicer. I do this this by checking if there is a single value in the current context using the HASONEVALUE function. If there is a single value then show the measure value, otherwise return BLANK. When you return BLANK Power BI will not show the whole row or data point which is exactly what we want. BLANK just like in Excel means that it won’t be shown. The measure simply looks like this:

Measure =
IF ( HASONEVALUE ( Country[Country] )SUM ( Sales[Units Sold] )BLANK () )

Next I add this measure to the visual and voila we get what we want:

But is it? How confusing is it for users to just see empty whitespace? Probably very. So the first thing I would do is add a background to make it a bit more obvious something should be there.

But still this not really clear is it? What if we could give the users some instructions? Well turns out we can. Of course we can with DAX :). So we will do the reverse as before, when no slicer value has been selected I want to show some text. So I will create a measure and use a Card visual to show the text. The measure looks like this:

measure text =
IF ( HASONEVALUE ( Country[Country] )“”“Please select a country” )

I set the background to the card to 100% opacity and now we can tell the user to select a country first:

When I do select a country the label disappears (again using BLANK):

So that is pretty much it. Some pretty neat tricks that might be useful.

4 Replies to “Show values in a chart only when a slicer is selected

  1. Hi Kasper,

    An alternative way of testing if a slicer is in play on a specific column is using the expression:

    CALCULATE(
    ISFILTERED( Country[Country] ),
    ALLSELECTED( Country[Country] )
    )

    So your measure could be written as:

    Measure =
    VAR CountryIsSliced =
    CALCULATE(
    ISFILTERED( Country[Country] ),
    ALLSELECTED( Country[Country] )
    )
    RETURN
    IF(
    CountryIsSliced,
    SUM( Sales[Units Sold] ),
    BLANK()
    )

    Hope it can be helpful for someone.
    Best regards Jes.

  2. Question! If we use the method to display nothing unless a slicer is picked will the speed to load the db be faster i.e. in matrix or table?

    1. I don’t think it will change the performance a lot but your mileage might vary depending on what the query\measures looks like

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.