Showing zero values?

[SaganBigRedDog]

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 Month'[Discrepancy Bis IDs])/DISTINCTCOUNT('! NEW ALL 401 OPEN Last Month'[Submission Bis IDs]))

and I tried adding +0 on the end but ended up with this (right)

How do I fix this?

[Anonymous]

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]

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]

@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]

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]

@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

[SaganBigRedDog · Accepted solution]

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!!

[vanessafvg]

maybe do a check to see if its blank first? but i am not sure if that creates the same issue

test =
IF (
ISBLANK (
CALCULATE (
DISTINCTCOUNT ( '! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs] )
/ DISTINCTCOUNT ( '! NEW ALL 401 OPEN Last Month'[Submission Bis IDs] )
)
),
0,
CALCULATE (
DISTINCTCOUNT ( '! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs] )
/ DISTINCTCOUNT ( '! NEW ALL 401 OPEN Last Month'[Submission Bis IDs] )
)
)

[SaganBigRedDog]

Hi,

Yes, it created the same issue.

Technically, those blank records are "0%" values.

ANy idea how to resolve?