Forum Discussion

SaganBigRedDog's avatar
SaganBigRedDog
Helper I
9 years ago
Solved

Showing zero values?

Hi!   So I have this chart (left) And I want it to show 0% for the blank values.   The measure formula is: Discrepancy Percent =  CALCULATE (   DISTINCTCOUNT('! NEW DISCREPANCY REPORT Last Mo...
Need Help
  • SaganBigRedDog's avatar
    SaganBigRedDog
    9 years ago

    Thanks, Charlie! I figured out the issue was with one of my tables with the FSS and District fields.  I used your formula after correcting and it worked! Thanks!!

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Instead of using the "/" operator to divide, try using DAX's DIVIDE(numerator, denominator, 0) in your existing formula. This will give you an actual zero-value instead of BLANK() if the denominator is zero.

 

For more information:

https://msdn.microsoft.com/en-us/library/jj677276.aspx

SaganBigRedDog's avatar
SaganBigRedDog
Helper I
9 years ago

This didn't work either.  I used:

 

Discrepancy Percent =
 DIVIDE(
  DISTINCTCOUNT('! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs]),DISTINCTCOUNT('! NEW ALL 401 OPEN Last Month'[Submission Bis IDs]),0)

 

but it still shows as blank:

 

For District 11, for example, ALyssa has 0 discrepancies and 16 submissions.  Should be 0/16. And should show as 0% on the report.

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    9 years ago

    SaganBigRedDog,

     

    Could you please create two measures to test the result?

    measure 1 = DISTINCTCOUNT('! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs])

    measure 2 = DISTINCTCOUNT('! NEW ALL 401 OPEN Last Month'[Submission Bis IDs])

     

    Regards,

    Charlie Liao

    • SaganBigRedDog's avatar
      SaganBigRedDog
      Helper I
      9 years ago

      Hi Charlie,

       

      I get the same thing using this.  Here is what it looks like with the measures and disc % set to Disc % = DIVIDE([measure 1],[measure 2],0)

       

      • v-caliao-msft's avatar
        v-caliao-msft
        Microsoft Employee
        9 years ago

        SaganBigRedDog,

         

        I have tested it on my local, we can use ISBLANK function to display 0 if the value is blank.

        Measure = IF(ISBLANK(DISTINCTCOUNT(Table1[Column1])),0,DISTINCTCOUNT(Table1[Column1]))

         

        You can use the DAX below for you first measure.

        measure 1 = IF(ISBLANK(DISTINCTCOUNT('! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs])),0,DISTINCTCOUNT('! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs]))

         

        If the issue persists, please provide us some sample data, so that we can make further analysis.

         

        Regards,

        Charlie Liao