Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
HI @Anonymous ,
Maybe you can try to add a page level filter with 'top' to control specific field rows.
Regards,
Xioaxin Sheng
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
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.
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
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.
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
Ok, I thought COUNTROWS(Sheet1) counted counted all the rows in the filtered table. But then it works.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |