Hiding a table if a count is less than N

[55chris55]

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

[sylviaadb]

I know it's been some years, but since I was looking for a solution to the same problem I thought that my solution might help others. I've dealt with the same issue as follows:

First I created this measure:

COUNTROWS(ALLSELECTED('table'[variable]))

Then I used this measure as a filter on the table, in my case: measure is greater than or equal to 3.

This results in the table not showing any data if the number of rows selected is 3 or less.

As a warning, I created an info button with a tooltip that says that the table won't show any data if less than 3 persons are selected. This solution worked for me, but there might be other ways to deal with this.

[bfong]

Thank you so much @sylviaadb, your answer saved me!

I was trying to filter on a table and couldn't get past the row-level filtering. Whereas for all other visuals I could get away with a Measure like if(count(Evaluations_f[Evaluation_Key])<5,0,1), I was at a loss for table data.

Thanks to you, I have now been able to put a new measure in place along the lines of CALCULATE(distinctcount(Comments_f[Evaluation_Key]),ALLSELECTED(Comments_f)), from which I can filter the data to my heart's content.

All the very best to you!

[Anonymous]

Is there maybe a new solution to this problem?

My workaround would be to use R or Python custom visuals, which includes ifelse statements.

i.e.:

sample_large_enough <- if(nrow(dataset) > 10, 1, 0)

ifelse(sample_large_enough ==TRUE){

--execute R code:

1) data transformation

2) visualization--

}else{

--execute R code to display warning message "subset too low"--

}

[v-jiascu-msft]

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

[Anonymous]

Hi Chris,

Do we have any solution for this now?

I have a same requirement to hide the complete visual if any record from it has <N value and I'm still not finding a way to hide a complete visual just with this condition.
Thanks in Advance.

Regards,

Prabha

[DataDiva]

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?