Limit table to display data only if 5 unique identifiers are included

[Anonymous]

Hello!

I have created a report that displays the results for survey data. Part of my responsibility is to make sure that privacy is protected. Because of this, I need to set a report-wide threshold for # of repsonses, below which no data is displayed.

For example, if one views the data "overall," there are several thousand repsonses. But if a filter is used to look at a smaller part of the group, it will include fewer responses.

I need the report to display nothing or ideally a "not enough data" error if a filter causes a visual to show data including fewer than 5 "count distinct" unique identifiers. This needs to apply to all visuals in all pages of the report. Does this make sense?

I'm hoping this is something that people have solved already - or something similar - although I wasn't able to find it in my searching. Thanks for your help!

[mahoneypat · Accepted solution]

I am not aware of a way to do that at the report level.  You can do it at the visual level with a Visual Level Filter, using a measure and then setting a min threshold.  FYI that if you do end up changing your existing measure, you can do it very quick with Tabular Editor.  Also, I would make a constant measure like Threshold = 5 and then reference that measure in all your updated measures.  That way, you can set it to 0 later if you don't want a threshold anymore (without changing them all again).

Pat

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

As @mahoneypat  said, the most effective way is to create a measure (if disticnctcount <5 ,"not enough data"). But to my knowledge, the Visual Level Filters section is the only one accepting a measure as a filter, whereas Page Level Filters and Report Level Filters only accept columns as a filter. So you could not apply measure to page-level filter. In this case, you may need to filter one by one.

About how to filter, please refer to:

Add a filter to a report in Power BI - Power BI | Microsoft Docs

https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@mahoneypat  Thanks for the follow-up. I've seen solutions like this posted elsewhere -- It looks like this would work for an individual measure. However, my report has already been created using some explicit and some implicit measures.

I'm wondering - is there a way to limit access at the full report level? I'd rather not go through and change a ton of measures if possible!

Thanks so much for helping with this.

[mahoneypat · Accepted solution]

I am not aware of a way to do that at the report level.  You can do it at the visual level with a Visual Level Filter, using a measure and then setting a min threshold.  FYI that if you do end up changing your existing measure, you can do it very quick with Tabular Editor.  Also, I would make a constant measure like Threshold = 5 and then reference that measure in all your updated measures.  That way, you can set it to 0 later if you don't want a threshold anymore (without changing them all again).

Pat

[Prama]

I have a similar situtation but when I tried the solution provided, my filter ignores the results by sentiment level which I don't want to happen. For eg, When the responses are filtered by country, I have 8 responses but if one or 2 responses were unfavourable or nuetral my visuals ignore it and shows 100% favorable result. If out 10, 8 respondents were unfavourable for 3 questions and the others responded favorable all questions, my visuals shows only the questions that has favourable and the 3 questions was shown as 100% unfavorable instead of it showing 80% unfavorable and 20% favorable. Please help me solve this. 

[mahoneypat]

Here is a measure expression that shows one way to approach that.  It will return blank if there are not at least 5 in scope.

MeasureIf5 = var result = [Your expression]

return IF(DISTINCTCOUNT(Table[ID]) >= 5, result)

Pat