Distinct count rows that are not blank!

[Anonymous]

Hey everyone,

How can I count all the distinct values in a column except the blank(null) values? I tried several things but nothing works.

[Anonymous]

any ways that doesn't require dax measure? purpose is, measure is not considered in drill through function

[mchapman]

Great work and thank you! Seems like not counting the blanks would be implied.  Yet another reason I'm having a hard time converting from MD to tabular

[jklemetsrud]

Doesn't look like this has been updated in a while. While the solution does in fact work, you now have the option of DISTINCTCOUNTNOBLANK
ie.

DIAD Count =

VAR _TheCount = CALCULATE

(

DISTINCTCOUNTNOBLANK('Equity Accounts'[DAID])

)

RETURN

IF(ISBLANK(_TheCount),0,_TheCount)

Just thought I'd share for anyone searching out there.

[Anonymous · Accepted solution]

=calculate( distinctcount(MyTable[MyColumn]), MyTable[MyColumn] <> BLANK())

[bajimmy1983]

Excellent @Anonymous. Thanks a lot!

[fahadfarooqi]

https://community.powerbi.com/t5/Desktop/Evaluation-of-data/m-p/203178#M89425

[XMonsterX]

Good solution...

One issue.. It if you have duplicate values it counts it as one. For example, if my table column is what people chose as a favorite animal, it would have a lot of people choosing dogs. There would be for example 50 dogs, but this formula would count the 50 instances of "Dog" as one. (Just a simple example). If there's 50, you want the count to reflect that.

MeasureHappy = calculate(count(MyTable[MyColumn]), MyTable[MyColumn] <> BLANK())

Changing from distinctcount, to just plainly count will resolve this, for anyone not looking to count dups as a singular. (If you want a zero instead of null

MeasureHappy = calculate(count(MyTable[MyColumn]), MyTable[MyColumn] <> BLANK()) + 0

Happy Intellegence

David

[Anonymous]

Hey @Anonymous

If the distinctcount of a column is equal to zero then distinctcount returns BLANK(null). Can I fix this to show 0 instead of null?

[Oxenskiold]

Hi @Anonymous,

you can use this:

=
CALCULATE ( DISTINCTCOUNT ( MyTable[MyColumn] ), MyTable[MyColumn] <> BLANK () )
    + 0

 <Blank> + 0 equals 0

Kind regards

Oxenskiold

[Anonymous]

Thanks @Oxenskiold! :))

[Anonymous]

Thanks a lot!!! 🙂