Forum Discussion
Exclude cells with small sample sizes
New to Power BI and trying to get my bearings. I'm working with survey (Likert-type) data in a university setting, and as a result, we are concerned about protecting the confidentiality of respondents. In the data in question, there are numerous demographic measures (e.g., race, gender, sexual orientation) that we would like to be able to include. However, as multiple identities are introduced (e.g., Asian, Transgender. and Pansexual) the sample size in many cells falls below low a threshold we are comfortable display (n > 5).
Coming from Tableau, I know you could write some code to calculate when that would happen and hide cells that contained small sample sizes. Is this possible to do in Power BI? I much prefer the experience with Power BI, but this is a big issue that I need to resolve before trying to convince my office to move over.
Thanks for any help/insight!
Hi, Select the other chart and Go to Edit Interactions (Format Menu) and Select Filter instead of Highlight.
Edit Interactions to Filter
10 Replies
Hi jbittinger, we encountered this same problem as we have sensitive healthcare data. We were able to successfully suppress small numbers through the use of DAX formulas. Conceptually, the formula goes like this: "If the number to be displayed is less than n, then display '***', otherwise, display the number." An actual example is here:
@JobsNoFilters = if( CALCULATE(COUNT(AppendAllPrograms[Job Number]))<=50,"***",CALCULATE(COUNT(AppendAllPrograms[Job Number]),ALL(AppendAllPrograms[Job Number])))CheersThis was the fix to our issue. Thanks!!
I simplified the DAX formula you provided and got the output we expected:FixedField1 = if(CALCULATE(SUM(Table1[Field1]))<=5, "***", CALCULATE(SUM(Table1[Field1])))
You could create a simple COUNT measure and then add that to your report filter and set it to be >=5. If I understand the problem correctly.
I think a picture will help here (and perhaps a COUNT measure is appropriate). In the picture below, I have selected women who have a low sense of belonging on the bottom right graph, which then highlights those women across the other graphs. I really like this feature, but some of the sample sizes are too small to display. For example, the cell I have circled has fewer than 5 respondents and I would want to hide that from being displayed.
I began playing around with creating a COUNT measure and was not getting far with it. For the image below, there are 4 variables being used: Diverse, Belonging, Gender, and Race. Would I essentially have to build a long if/then logic calculation to use the COUNT measure?
I believe I duplicated this. I created the following data:
EthnicityCategory
Asian 1 Asian 1 Asian 1 Asian 1 Asian 1 Asian 2 Asian 2 Asian 2 Asian 2 Asian 2 Asian 3 Asian 3 Asian 3 Asian 3 Asian 3 Asian 4 Asian 4 Asian 5 Asian 5 Asian 5 Asian 5 Asian 5 Black 1 Black 1 Black 1 Black 1 Black 1 Black 2 Black 2 Black 3 Black 3 Black 3 Black 3 Black 3 Black 4 Black 4 Black 4 Black 4 Black 4 Black 5 Black 5 Black 5 Black 5 Black 5 I then put Ethnicity in my Axis, Category in my Label and then Count of Category (just changed the default summation from SUM to COUNT) in my Value. Then, in my filter area for the visual, I set "Count of Category" to "greater than or equal to 5"
