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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors