Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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.  
  • Anonymous's avatar
    Anonymous
    9 years ago

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

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

 

XMonsterX's avatar
XMonsterX
Advocate II
9 years ago

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