Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
55chris55
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
5 REPLIES 5
sylviaadb
New Member

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.

rokasklydzia
Advocate I
Advocate I

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
Employee
Employee

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.

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.