Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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!!
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:
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.
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
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)
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
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!!
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] )
)
)
Proud to be a Super User!
Hi,
Yes, it created the same issue.
Technically, those blank records are "0%" values.
ANy idea how to resolve?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |