How to obtain count (or %) of 0 for columns where some (not all) rows are 0

[afaherty]

Apologies if my subject line is confusing - I didn't know how to word this!

I have matrices that show distinct counts and bar graphs that show percentages. I wanted 0 to show when the counts (or percentages) are 0 so I created a dim table to use in the graphs. This works for columns where there are some non-zeros BUT I don't want the columns that have ALL zeros to show at all. I just want them to NOT be there. For example:

Here are my measures:

Distinct Counts: IndexUniqueCount = DISTINCTCOUNT('Table'[Index])+0

Percentages:  County % = DIVIDE ([IndexUniqueCount],CALCULATE([IndexUniqueCount],ALL('Table'[County],'Table'[County])))

One of my bar graphs has a more complicated percentage calculation because we're asking the survey respondents to tell us about whether the county they work in has any unique characteristics from a list:

Unique Characteristics % =

VAR _numerator =

DISTINCTCOUNT ('Table'[Index])

VAR _selectedCounty =

ALLSELECTED ('Table'[Survey Items])

VAR _denominator =

CALCULATE (

DISTINCTCOUNT ('Table'[Index]),

TREATAS (_selectedCounty, 'Table'[Survey Items]),

REMOVEFILTERS ('Table'[Unique Characteristics])

)

RETURN

DIVIDE (_numerator +0, _denominator)

Thanks everyone!

[Nasif_Azam]

Hey @afaherty ,

You're facing a common issue in Power BI matrix and bar visuals you want to show zero values within a group, but hide entire columns (i.e., categories like counties) where all values are zero. You need to create a custom filter measure that returns TRUE() only if at least one non-zero value exists for that column (e.g., county). You can then apply this filter to the visual to hide columns where all values are zero.

 

1. Create a helper measure

This measure will evaluate whether the selected County has any non-zero data.

ShowCounty =
IF(
    CALCULATE(
        DISTINCTCOUNT('Table'[Index]),
        REMOVEFILTERS('Table'[Survey Items]) -- or other dimensions as needed
    ) > 0,
    TRUE(),
    FALSE()
)

You might also want to replace DISTINCTCOUNT('Table'[Index]) with the actual measure you’re using, like [IndexUniqueCount], if it's more relevant to your context.

2. Apply this measure as a visual-level filter

• Select the matrix or bar chart.

• Drag ShowCounty to the Visual level filters pane.

• Set the filter to: is TRUE.

This will ensure that only counties with at least one non-zero value across all rows/groups will show up in the visual.

 

Things To Remember:

• Don't remove zero values inside the matrix. Keep them your original intention to show 0 instead of blanks is correct for rows with partial data.

• This method works well with both matrices and bar charts, as long as the category axis is filtered by this helper measure.

• You may need to adjust granularity depending on how many slicers/filters your report supports.

For Detailed Information:

DAX Filter Functions - Official Docs

Power BI Community – Hide columns with zero values

Stack Overflow – Power BI visual filtering

SQLBI

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[afaherty]

All of the results come out as "True" it appears..

[afaherty]

Hi, Thank you so much for your assistance. I followed your directions and placed the new filter measure onto the visual but there are no True or False options when expanded:

[bhanu_gautam]

@afaherty You can modify the IndexUniqueCount measure to return BLANK() when the count is zero.

IndexUniqueCount =
VAR CountValue = DISTINCTCOUNT('Table'[Index])
RETURN IF(CountValue = 0, BLANK(), CountValue)

Similarly, for the County % measure, you can modify it to return BLANK() when the percentage is zero.

DAX
County % =
VAR CountValue = [IndexUniqueCount]
VAR TotalCount = CALCULATE([IndexUniqueCount], ALL('Table'[County], 'Table'[County]))
RETURN IF(CountValue = 0, BLANK(), DIVIDE(CountValue, TotalCount))

DAX
Unique Characteristics % =
VAR _numerator = DISTINCTCOUNT('Table'[Index])
VAR _selectedCounty = ALLSELECTED('Table'[Survey Items])
VAR _denominator = CALCULATE(
DISTINCTCOUNT('Table'[Index]),
TREATAS(_selectedCounty, 'Table'[Survey Items]),
REMOVEFILTERS('Table'[Unique Characteristics])
)
RETURN IF(_numerator = 0, BLANK(), DIVIDE(_numerator, _denominator))

[afaherty]

Hi, thank you for your help. The problem is that I don't want the Caroline County column in the matrix above to show blanks if zero, I want them to show zero.