Limit filtering/slicing options to minimum number of selected rows

[Anonymous]

Hi,

Is there a way to prevent a viewer of a power bi report, with several filter or slicing options, from filtering too narrowly, i.e. not to a selection smaller that a set number of rows? I am interested in in this because the report visuals (column chart and table) will show sensitive user data, so it is needed for privacy preservation. Currently I am using range slider slicers and categorical selection slicers.

The only idea I had to achieve this was to switch to a python visual where I dynamically can calculate the selected number of rows and don't update the graph (or clear it) if the number is too low, and possibly add some waring message. But then I can't use the interactive features in the native charts. Also, I would be much better if the actual slicing visuals updated dynamically and prevented a too narrow selection.

Thanks

[Anonymous]

HI @Anonymous ,

Maybe you can try to add a page level filter with 'top' to control specific field rows.
Regards,

Xioaxin Sheng

[lc_finance]

Hi @Anonymous ,

yes it's possible. You can do that with a COUNT formula.

I proposed a similar solution a few days ago. You can find the discussion here:

https://community.powerbi.com/t5/Desktop/Visual-level-filter-based-on-counted-values/m-p/820752

Does that help you?

LC

Interested in Power BI and DAX formulas? Check out my blog at www.finance-bi.com

[Anonymous]

Hi @lc_finance ,

That should work for me but I'm having some trouble getting the conditional visualisation to work for my clustered column chart.

What does work is this measure

cond_meas = IF(TRUE(), CALCULATE([MyPrivateMeas]))

 but when I replace it with this

cond_meas = IF(CALCULATE([filter_count] > 5), CALCULATE([MyPrivateMeas]))

 it always seem to return blank which is the false case, event if filter_count is greater than 5. I have verified that the filter_count measure is above 5 in another visual, and changes when I tweak the slicers.

[lc_finance]

Hi @Anonymous ,

That might depend on the evaluation context of [filter_count], i.e. in which context Power BI calculates it.

Can you share your Power BI file via OneDrive or similar? I'll take a look 

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Anonymous]

Thanks, I created a minimal example, you can the the project file here https://drive.google.com/file/d/1y9YgVk5OB0ZmoxYy8LsLE9EwXJf6SesW/view?usp=sharing

As you can see, the visual is not shown even if filer_count is above 5. Only when the IF-expression in MyPrivateMeas is replaced with TRUE() it will show.

[lc_finance · Accepted solution]

Hi @Anonymous ,

You can find an updated example here.

Your bar chart is by category (A, B, C) which means the count is done by category.

In your sample data, A has 2 rows, B has 2 rows and C has 3 rows. All of them have less than 5 rows, so nothing is shown. 

I added a table (Sheet2) with 6 rows for C. With the new table, C has more than 5 rows thus data is shown for C.

Let me know if this helps you,

LC

Interested in Power BI and DAX? Check out my blog at www.finance-bi.com

[Anonymous]

Ok, I thought COUNTROWS(Sheet1) counted counted all the rows in the filtered table. But then it works.