Forum Discussion

elopez's avatar
elopez
Helper I
5 years ago

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.

 

'

 

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?

 

 

Need Help

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    5 years ago

    try

     

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

    • elopez's avatar
      elopez
      Helper I
      5 years ago

      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)

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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

    • elopez's avatar
      elopez
      Helper I
      5 years ago

      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)?