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

     

11 Replies

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks a lot!!! :)

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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?

      • Oxenskiold's avatar
        Oxenskiold
        Advocate I
        9 years ago

        Hi Anonymous,

         

        you can use this:

         

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

         <Blank> + 0 equals 0

         

        Kind regards

        Oxenskiold

  • jklemetsrud's avatar
    jklemetsrud
    New Member
    4 years ago

    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.
  • mchapman's avatar
    mchapman
    Advocate I
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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