Forum Discussion

sanjeev803's avatar
sanjeev803
Icon for Helper I rankHelper I
7 years ago
Solved

DAX - Ignore 0 in count

Hi All,

 

I need some help on DAX funtion. I want to get the count of all non zeros values by brand.

My existing data is :

BrandWeb Estate Value
A78
A52
A0
A0
A56
B53
B79
B0
B50
C36
C0
C30
C49
D49
D0
D77
D60
D30
D

72

 

And I want to create a measure with the help of which i can get output like below.

 

Brand  Web Estate Value
A         3
B         2
C         4
D         4

 

Any help on this highly appriciated.

 

Best Regards,

Sanjeev

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    sanjeev803 based on your data, your output result in your post is wrong. Anyhow here is the measures

     

    Zero by Bank = 
CALCULATE( 
COUNTROWS( Table2 ), 
ALLEXCEPT( Table2, Table2[Band] ), Table2[Web Estate Value] = 0 
)

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    sanjeev803 based on your data, your output result in your post is wrong. Anyhow here is the measures

     

    Zero by Bank = 
CALCULATE( 
COUNTROWS( Table2 ), 
ALLEXCEPT( Table2, Table2[Band] ), Table2[Web Estate Value] = 0 
)
    • sanjeev803's avatar
      sanjeev803
      Icon for Helper I rankHelper I
      7 years ago

      parry2k Thanks for your understanding. I was looking for non zero count and replaced = with <> ang got the desired result.

       

      Thanks much.

       

      Cheers!

      Sanj