cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Distinct count rows that are not blank!

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

9 REPLIES 9
New Member

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.

VAR _TheCount = CALCULATE
(
DISTINCTCOUNTNOBLANK('Equity Accounts'[DAID])
)
RETURN
IF(ISBLANK(_TheCount),0,_TheCount)

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

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

Excellent @Anonymous. Thanks a lot!

Frequent Visitor
Frequent Visitor

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
Not applicable

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?

Hi @Anonymous,

you can use this:

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

<Blank> + 0 equals 0

Kind regards

Oxenskiold

Anonymous
Not applicable

Thanks @Oxenskiold! :))

Anonymous
Not applicable

Thanks a lot!!! 🙂

Announcements