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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
elopez
Helper I
Helper I

Replacing missing values in matrix table with '0'

I am making quite a few matrix tables similar to the one below. I want to replace the blank fields with '0'. However, when I try to use different calculated measures, nothing happens or I get an error.

 

elopez_0-1604950727757.png'

 

1) Number of Faculty = IF(CALCULATE(ISBLANK(COUNT('FT-cal-acad-faculty only'[Faculty Rank]))),0,COUNT('FT-cal-acad-faculty only'[Faculty Rank])). nothing happens 

 

2) Measure = Calculate(COUNT('FT-cal-acad-faculty only'[Faculty Rank]]+0,'FT-cal-acad-faculty only'[Number of Faculty] in DISTINCT('Replacing 0''s'[Faculty Rank]))). Get the error 'The COUNT' function only accepts a column reference as an argument.' 
 
3)Measure = IF(ISBLANK(['FT-cal-acad-faculty only'[Faculty Rank]]),0,['FT-cal-acad-faculty only'[Faculty Rank]]). Get the Error 'FT-cal-acd-faculty only; [Number of Faculty]: The function SUM cannot work with values of type String.' 
 
It seems like because the values are String types I cannot use the Count nor Sum functions in my measures. What is a workaround or solution to this?

 

 

4 REPLIES 4
mahoneypat
Microsoft Employee
Microsoft Employee

Your 2nd measure is missing the ) after the +0.  But why can't you just use this expression?

 

NewMeasure = COUNT('FT-cal-acad-faculty only'[Faculty Rank]])+0

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Its giving me the error 'A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed'

elopez_0-1605020956423.png

When i posted before, someone told me to create a separate table and then use it in this measure. here's the table i created (called Replacing 0's) which I reference at the end of the measure you corrected for me :

elopez_1-1605021183797.png

I'm not sure if this separate table is needed and if I can just use a measure referencing my main dataset (i.e. is there a way to not use the 'Replacing 0's table)?

vanessafvg
Super User
Super User

try

 

measure =
IF (
    ISBLANK ( COUNT ( 'FT-cal-acad-faculty only'[Faculty Rank] ) ),
    0,
    COUNT ( 'FT-cal-acad-faculty only'[Faculty Rank] )
)





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




It didn't do anything, same as my option 1. it just again displays the same values without replacing any blank fields with '0' (i get the exact same table as in the picture above, with blank fields)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.