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.

Let’s block ads! (Why?)

Kasper On BI