Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jbittinger
Regular Visitor

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!

1 ACCEPTED SOLUTION

@jbittinger

 

Hi, Select the other chart and Go to Edit Interactions (Format Menu) and Select Filter instead of Highlight.

 

Edit Interactions to FilterEdit Interactions to Filter




Lima - Peru

View solution in original post

10 REPLIES 10
TimDyeson
Frequent Visitor

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])))
 
Cheers

This 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])))
Greg_Deckler
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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?

powerbi-smallsample.PNG

I believe I duplicated this. I created the following data:

 

EthnicityCategory

Asian1
Asian1
Asian1
Asian1
Asian1
Asian2
Asian2
Asian2
Asian2
Asian2
Asian3
Asian3
Asian3
Asian3
Asian3
Asian4
Asian4
Asian5
Asian5
Asian5
Asian5
Asian5
Black1
Black1
Black1
Black1
Black1
Black2
Black2
Black3
Black3
Black3
Black3
Black3
Black4
Black4
Black4
Black4
Black4
Black5
Black5
Black5
Black5
Black5

 

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"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks for the help thus far. When I try this, the problem still exists because it doesn't seem to affect the highlighted numbers (see below). I got to this point by clicking on a bar segment from a belonging graph.

smallsample.png

I don't have any context in relation to this. Is this visualization configured to have a filter of "Count of Diverse" > 5?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Yes, the visual has a Count of Diverse > 5 filter.

I've pulled a sample from the data and created a sample page. The .pbix file can be accessed here.

 

@jbittinger

 

Hi, Select the other chart and Go to Edit Interactions (Format Menu) and Select Filter instead of Highlight.

 

Edit Interactions to FilterEdit Interactions to Filter




Lima - Peru

Thanks for this solution, I take it there's no way to do this and allow the highlighting option (makes sense, but I was still hopeful).

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.