Using DAX to hide entries in table if there are low counts

[WillP]

Hi all,

I have a Power BI table constructed using data with complex relationships. It allows a comparison of counts of distinct user IDs based on two data sources, one of which has RLS based on the user logged in. The problem I am having is that when counts are low in the table (<5), I would like to be able to hide the values. If there is only one value hidden, I must also hide another value to prevent back-calculation of the first hidden value.

The outcomes have three possible values (Y (Yes), N (No), and Not answered). Thus, if the count of Yes is <5 (and the others are both above 5) I want to hide the next biggest count as well (or any blank). The tables are broken down and filtered, and both these functionalities are controlled by slicers. Add in the RLS and it's all a bit complicated and I am not particularly great at DAX. I would ideally like the suppressed values (blanks, <5 or additionally hidden values) to be replaced by 'SV' (to indicate suppressed/hidden values).

I have shared an example pbix file here: https://drive.google.com/drive/folders/1oHDGSgUSM6CxQMD3LauerE4G6lVVKWj8?usp=sharing

Any suggestions would be gratefully received,

Will

[ValtteriN]

Hi,

You can create a filter measure to achieve this. E.g. Here I want to hide the "not answered"

Dax:

Measure 32 = IF(COUNTROWS('Table (17)')<5,1,0)
Here we simply count the rows on the filter context. If we place the measure to the example table result is as follows:
Now we can use this result to dynamically filter the results:

To hide the second biggest value we can expand this logic:

Measure 32 = COUNTROWS('Table (17)')

Measure 33 = IF(

    RANKX(ALL('Table (17)'),[Measure 32],,DESC)=1,1,0)

Now the answer with most answers will get value 1 and we use that to our advantage to show only that answer.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[WillP]

Hi Valtteri,

Unfortunately, when I try to use RANKX in my example, it just keeps churning and eventually runs out of memory on my laptop. 

Will

[WillP]

If anyone else comes across this post and has any ideas, I would love to hear them. Unfortunately, I haven't managed to make any progress 😕

Will

[WillP]

Going to delete my example pbix now. I haven't received a solution and don't want to leave it there for eternity. If anyone wants to have a go at this in future, please leave a reply.