cancel
Showing results 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

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

11 REPLIES 11
Frequent Visitor

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

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

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

Helper V

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

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors