cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Hiding a table if a count is less than N

I'm sure this must be a common problem but I can't find the solution.  I need to hide a table or visual if the combined slicer selection(s) (e.g. gender,age,department,grade,location) drop below a minimum count (e.g. 10 ) and give the User a warning as to why the table is hidden.  This is so that confidentiality of very small response groups is protected when drilling down through different demographics.

I have the idea of creating two bookmarks: "Normal" and "Minimum Threshold".  In "Normal" view the table or visual is shown with a simple card by the side of it that shows the current number of respondents.  When the number of respondents drops below 10 for any given set of slicer selections then a "Minimum Threshold" bookmark hides the table/visual and the card changes in size and replaces the table/visual with a big warning market "Minimum Threshold - Confidentiality Protected"

I have created the bookmarks, together with the following measure for the card:  

Minimum Data = if(Demographics[Respondents]>10,Demographics[Respondents],"Minimum Data - Warning")

It all works just as I would like but I can only toggle the Bookmarks manually.  How can I automatically call a Bookmark depending on the result of a measure?  In the example above as long as the Minimum Data is >10 we use the "Normal" bookmark and if it <11 we use the "Minimum Threshold" bookmark.

Is this the best way to achieve this, or is there a more elegant solution that hides the data and informs the User why the data is hidden?

Many thanks
Chris
2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Hiding a table if a count is less than N

Hi Chris,

 

I'm afraid there isn't such a function that can hide a visual for now. There is a workaround. We can add a measure to the visual and filter out the values "Minimum Data - Warning" in the Visual Level Filter. 

Can you share a small sample, please? Please mask the data first. The data model structure is the critical part when creating such a measure.

 

Best Regards,
Dale

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

Re: Hiding a table if a count is less than N

Instead of using bookmarks, perhaps you could write a formula that basically says:

if(sum([count])<10, 'Too Few', [Value]

 

That would mask the individual cells, and you'd have to work on the "count" field to be an accurate representation of what shows up. 

 

I was also wondering if there is a way to force a certain number of slicer selections so that you don't have peer groups of less than X number. In other words, that if only four selections were made in a slicer, then data wouldn't show but if five or more are made, then the table populates? 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors
Top Kudoed Authors