Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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())

 

View solution in original post

11 REPLIES 11
CharlesEugenio1
Frequent Visitor

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

mchapman
Advocate I
Advocate I

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
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.

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

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

 

Excellent @Anonymous. Thanks a lot!

Jaderson Almeida
Business Coordinator

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!!! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.