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!

[v-prasare]

@afaherty As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

Hi @afaherty,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

Hi @afaherty,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

@Nasif_Azam &@bhanu_gautam Thanks for your prompt response.

Thanks,

Prashanth Are

MS Fabric community support.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

[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:

[Nasif_Azam]

Hey @afaherty ,

Thanks for the update and screenshots they were very helpful! It looks like the reason you're seeing only "True" for all counties is that the filter logic isn't evaluating at the correct granularity. Let’s refine it so that it checks each County individually and returns FALSE when all rows/groups within a county are zero. Try the updated measure:

ShowCounty :=
CALCULATE(
    MAXX(
        VALUES('Table'[Group]),  // Replace with your row-level category
        IF([IndexUniqueCount] > 0, 1, 0)
    ),
    ALLEXCEPT('Table', 'Table'[County])  // Keeps County context only
) = 1

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

[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.