cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: Distinct count rows that are not blank!

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

 

View solution in original post

8 REPLIES 8
Highlighted
Memorable Member
Memorable Member

Re: Distinct count rows that are not blank!

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

 

View solution in original post

Highlighted
Helper II
Helper II

Re: Distinct count rows that are not blank!

Thanks a lot!!! 🙂

Highlighted
Helper II
Helper II

Re: Distinct count rows that are not blank!

Hey @scottsen

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?

Highlighted
Advocate I
Advocate I

Re: Distinct count rows that are not blank!

Hi @BiMK,

 

you can use this:

 

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

 <Blank> + 0 equals 0

 

Kind regards

Oxenskiold

Highlighted
Helper II
Helper II

Re: Distinct count rows that are not blank!

Thanks @Oxenskiold! :))

Highlighted
Frequent Visitor

Re: Distinct count rows that are not blank!

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

Highlighted
Frequent Visitor

Re: Distinct count rows that are not blank!

Highlighted
Resolver I
Resolver I

Re: Distinct count rows that are not blank!

Excellent @scottsen. Thanks a lot!

Jaderson Almeida
Business Coordinator

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors