Count distinct values plus count each blank one time DAX

Question

Hello,&nbsp;I have a column with purchase document numbers. They can be blank or have a number that can appear repeated in several rows.I want to count the repeat values one times and each blank also one time (no count all blanks just one time).&nbsp;Is this possible to do with a measure? I am using the COUNTDISTINCT but this will not work for the blanks counting.

camargos88 · Answer

AnaPiedade&nbsp;,
&nbsp;
Try this function:
https://docs.microsoft.com/en-us/dax/countblank-function-dax

anapiedade · Answer

Hello camargos88,&nbsp;Thank you for the suggestion. Although this function is only counting blanks and gives me a much bigger number for the blanks I have.&nbsp;purchdoc&nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12blankblank&nbsp;My desired outcome is 4 for the example above.

camargos88 · Answer

AnaPiedade&nbsp;,
&nbsp;
Try this one:
&nbsp;
_C_Blank = IF(COUNTBLANK('Table'[Column1]) &gt; 0, 1, 0)
&nbsp;

anonymous · Answer

AnaPiedade&nbsp;You can use DISTINCTCOUNTNOBLANK() funtion to get the nonblank values, and then add the count of blanks to it with&nbsp;COUNTBLANK(), so it is 2 + 2 = 4.
&nbsp;

Measure = DISTINCTCOUNTNOBLANK('Table'[Column1])+COUNTBLANK('Table'[Column1])

Paul Zheng _ Community Support TeamIf this post helps, please Accept it as the solution to help the other members find it more quickly.&nbsp;&nbsp;

Forum Discussion

Count distinct values plus count each blank one time DAX

4 Replies

Related content

count distinct

Distinct Count

Distinct count

DAX Calculation for Distinct Count

Count Distinct Values based on another column in Power Query