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 : Brand Web Estate Value A 78 A 52 A 0 A 0 A 56 ...
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 
)
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

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

      sanjeev803 aha, sorry I missed that. Anyhow you have the solution :)

    • Jay2022's avatar
      Jay2022
      Icon for Helper IV rankHelper IV
      2 years ago

      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