DAX - Ignore 0 in count

[sanjeev803]

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 :

Brand	Web Estate Value
A	78
A	52
A	0
A	0
A	56
B	53
B	79
B	0
B	50
C	36
C	0
C	30
C	49
D	49
D	0
D	77
D	60
D	30
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

[parry2k · Accepted solution]

@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]

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

Thanks much.

Cheers!

Sanj

[Jay2022]

HI how would i add 2 counts together for 2 seperate tables would it be something along the line of 

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

bringing up errors unfortunatley

[parry2k]

@sanjeev803 aha, sorry I missed that. Anyhow you have the solution 🙂