cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Visualisations blank until something selected in a slicer

The default functionality for a slicer in Power BI seems to be that if nothing is selected in a slicer then all records are shown in visualisations on the same report where the interactions are set as filter. What I would like to be able to do is not show anything in these other visualisations until one or more entries are selected in the slicer. Is this possible?

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @Dave0145,

 

For your requirement, you can create a measure like this:

 

SlicerCheck = =if(calculate(distinctcount([SlicerColumn]),allselected([SlicerColumn]))=1,"Y","N")

 

Then place this measure in a table visual, set filter condition as is Y. If you want to select multiple values in a slicer, set is N.

 

For more information, see:

Empty table with a slicer

visual filter problem--unpredictable behavior

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

22 REPLIES 22
Regular Visitor

Try and use an integer instead of a string ie. substitue "Y" with 1 and "N" with 0. Worked for me 🙂

Helper III
Helper III

Hi everyone,

I wrote a comment because this technique was working but only on some visuals and not on everything. So i applied another technique transforming my variables in Measure that are calculated based on a fact a filter is selected and use this measure in the visualisation, not the variable filtered on a measure.

In my case i want the table Metrics to be filtered on one and only one metric and show the progress realised in the table Progress Report:

SelectedProgress = If(calculate(distinctcount(Metrics[Title]);ALLSELECTED(Metrics[Title]))=1; CALCULATE(Sum('Progress Report'[Progress]);ALLSELECTED('Progress Report'));Blank())

Seems to work fine for my necessities, cannot assure it solves all problems
Community Support
Community Support

Hi @Dave0145,

 

For your requirement, you can create a measure like this:

 

SlicerCheck = =if(calculate(distinctcount([SlicerColumn]),allselected([SlicerColumn]))=1,"Y","N")

 

Then place this measure in a table visual, set filter condition as is Y. If you want to select multiple values in a slicer, set is N.

 

For more information, see:

Empty table with a slicer

visual filter problem--unpredictable behavior

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

how can I hide " No data available to display" when doing this measure filter please

Like @joshcomputer1  and other that have comented, 
this tecniques works for some vizuals but not for others, i'm able to put the measure in the filter pane section for the other vizuals but i cannot click or edit it, it seems block and i don't understand why...

Did anyone got why?

 
 
 
 
Anonymous
Not applicable

What would the work around be for a Measure, not a column? 

I created a measure to do this. The measure counts the specific number of dates selected. I figure I can use this to filter based on the number of dates selected, so basically "plot data only when 10 or fewer days are selected".

 

Date Filtered = calculate(DISTINCTCOUNT('Data'[Date]),ALLSELECTED('Data'[Date]))

I have a card on the report that shows the measure and it works appropriately. When I have nothing selected it counts all the unique dates (days). When I select one, two or whatever it counts correctly.

 

But when I add this to the filter on a visual it doesn't work. It's calculating the measure for each group of data (which is grouped by day and hour using the bin feature), meaning it always returns 1. How do I make this calculate for the whole page instead of per row/group in the chart?

So it's counting based on the grouping of the values rather than the distinctcount of dates? Can you use allexcept() in your measure and put the groupings name in there so it won't point to that element? Might be worth a shot. 

Hm... I tried 

 

Date Filtered = calculate(DISTINCTCOUNT('Data'[Date]),ALLSELECTED('Data'[Date]),allexcept('Data','Data'[Date]))

But it behaves the same way.

Excellent...that really helped...BUT where I have only one value in the SLICER Table it always reutrn the slicercheck to Y. Is there a way to get the same functionality with just one value in the slicer.

 

e,g, I have only one value. "SHOW' in the slicer and when the user select/clicks show on slicer only then it should display the data on visual and when the user deselect the SHOW on slicer it should reset the data. like I am trying to get the toggle button functionality from this.  Thanks

 

 

Regards,
Raheel

@RAHEEL

 

Maybe the slicer needs to be single select? When nothing is selected, it should work the same as a list with many options. I would test your measure on a card to see if when the slicer is used or not used, the card will switch.  This functionality is sketchy. I never got it to fully work as intended with all measures. I wish there was a field in the controls to just "when nothing selected" display this "".

Hi @NWBI,

 

 I don't know if what you are trying to show is a measure or not, but if there is a DAX formula for it, then you can use the expression hasonefilter as a conditional statement (similar to iserror in excel) so you can set it to zero if no filter. I am sure you can type the word "Blank" and it will display, but not sure if you place "" it will actually be blank. Worth giving it a shot. 

Just added Slicer Check as a tooltip on the charts I wanted to blank out, and it let me filter OK, 

 

Thanks!

I'm not quite sure how this solves the problem. I thought I had a similar problem to OP. I need to have a table that is blank until someone selects something in the slicer (one or more values). This solution works fine for a slicer when multiple values cannot be selected. But when multiple values are selected, it does not work.

 

The formula is checking if there is one value selected. If we select one value this is fine, but multiple selected values are treated the same way as no value selected, this is where the problem is.

I added the SlicerCheck to a test card to confirm it was picking up the slicer correctly and it shows a Y when sliced and N when not sliced. (which is great!).  When I add the SlicerCheck to my other card (the one I actually need it to work on), I am adding it to the visual level filters area, it doesn't give me options to filter by. I can click the arrow to expand but I can't type in the contains box underneath.  

 

this works on isolated card

SlicerCheck = if(calculate(distinctcount('DataSet'[Analyst]),allselected('DataSet'[Analyst]))=1,"Y","N")

 

My slicer is just a list of analyst names 'Dataset'[Analyst]

 

Please help me find out why the filtering isn't allowing me to click on anything.  *if the funcationality isn't there for filter a card's visual then it shouldn't have the option to add there. 

 

Can a KPI be used for this same purpose? Do it allow for the visual filter to be entered?

@joshcomputer1 are you able to solve that problem. I am facing the same problem. I need to show the text of selected name from the slicer on the card. Otherwise slicer should be hidden.But I am not able to add the filter on the card visual.

 

Anonymous
Not applicable

@v-qiuyu-msft - great solution, thanks! +1

 

I did note that if you bring the measure onto a table as a column, then the calculation slows down considerably, so best to have it only on the visual level filter.

 

I noted also that you can't have it on the Page level or Report level filter, but you can of course drag it onto the visual level filter for other tables.

Hello,

 

I've used this solution to hide a claster column chart and it works.

I've also tried to hide a card with text inside but the visual filter is not working, I can't choose anything, it's blocked.

Can anybody suggest what can be done in that situation?

Is it impossible to use a measure-filter in a card?

 

What I want to achieve?

I want the card to be invisible until a room on a visual is chosen.

 

 

Przechwytywanie.PNG

thank you - that works.

Impactful Individual
Impactful Individual

The Curbal video at https://www.youtube.com/watch?v=gYbGNeYD4OY&t=52s, starting at about 04:15, shows how to create a blank in a visualization until a slicer value is selected. 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors