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.
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.
This is excellent. This solves the issue of “what happens if they sellect more that ine country”!!
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?
I don’t think it will change the performance a lot but your mileage might vary depending on what the query\measures looks like
What about table , I want the same for table visualisation here you have taken for tree map visual right but I need above table visual. How to display table visual only when we select slicer only. Please upload the working file if possible.