Forum Discussion

tcmem's avatar
tcmem
Regular Visitor
8 years ago
Solved

How to include Zeros in column value count

I'm trying to calculate a count of items in a column that are less than or equal to 12. For some reason, it's excluding the Zeros. How do I include the Zeros in my count? I'm also excluding blanks ba...
Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    tcmem

     

    Hi, you can use:

     

     

    Measure =
COUNTROWS (
    FILTER ( Table1, ISBLANK ( Table1[Field2] ) = FALSE () && Table1[Field2] <= 12 )
)

     

     

    Measure =
CALCULATE (
    COUNT ( Table1[Field2] ),
    FILTER ( Table1; ISBLANK ( Table1[Field2] ) = FALSE () && Table1[Field2] <= 12 )
)

     

    Regards

     

    Victor

    Lima - Peru

tcmem's avatar
tcmem
Regular Visitor
8 years ago

Here's an example of my data:

 

Field1Field2
Acct113
Acct2 
Acct31
Acct45
Acct5 
Acct63
Acct70
Acct812

 

Based on my criteria of counting Field1 where Field2 is <= 12 and NOT BLANK, the results should be a count of  5.

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
8 years ago

tcmem

 

Hi, you can use:

 

 

Measure =
COUNTROWS (
    FILTER ( Table1, ISBLANK ( Table1[Field2] ) = FALSE () && Table1[Field2] <= 12 )
)

 

 

Measure =
CALCULATE (
    COUNT ( Table1[Field2] ),
    FILTER ( Table1; ISBLANK ( Table1[Field2] ) = FALSE () && Table1[Field2] <= 12 )
)

 

Regards

 

Victor

Lima - Peru

  • tcmem's avatar
    tcmem
    Regular Visitor
    8 years ago

    Hello Victor,

     

    I used the second suggestion and it worked perfectly. :-) Thank you so much for your help!

     

    Measure =
    CALCULATE (
        COUNT ( Table1[Field2] ),
        FILTER ( Table1, ISBLANK ( Table1[Field2] ) = FALSE () && Table1[Field2] <= 12 )
    )