How to include Zeros in column value count

[tcmem]

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 based on two different columns. The results would be correct if the Zeros were included in the count.

I'm attempting to create a new measure because I need to do a calulation using the results. Please see an example below.

Less than 12 or Blank = CALCULATE(count('Table1'[Field1)]),'Tabel1'[Field1)]<>BLANK(),'Table1'[Field2]<>BLANK(), 'Table1'[Field2]<=12,'Table1'[Field2]=0)

I'm really new to using Power BI on this level. Any suggestions are appreciated.

[pxg08680]

@tcmem

Left table is the raw data with zeroes and blank values and right side table shows you count with blank values also.

[tcmem]

Thanks for your reply! I would also like to exclude blanks from 'Table1'[Field1] as well.

[sevenhills]

Column = IF( Table1[Column1] <> BLANK () , CALCULATE( COUNT (  Table1[Column1]), Table1[Column2] <= 12 || Table1[Column2] = BLANK () ))

[tcmem]

Unfortunately, neither suggestion worked. I created another table which pulls all fields from the original table, but excludes the rows where Field1 is BLANK. This eliminates one needed filter. However, it's still not including the Field2 = Zeros in the total count with Field2 <=12. I created this formula to count Field1 if Field 2 is NOT BLANK and is <= 12.

Less Than 12 or Blank  = CALCULATE(COUNT(Table1'[Field1]),'Table1'[Field2] <= 12 && 'Table1'[Field2] <> BLANK())

If this would include the occurrences of Field2 = 0 in the count, this would yield the results I'm trying to achieve.

Thanks for your help thus far!

[tcmem]

Here's an example of my data:

Field1	Field2
Acct1	13
Acct2
Acct3	1
Acct4	5
Acct5
Acct6	3
Acct7	0
Acct8	12

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

[Ashish_Mathur]

Hi @tcmem,

This will also work

=CALCULATE(COUNTROWS(Data),Data[Field2]<=12)-COUNTBLANK(Data[Field2])

Hope this helps.

[tcmem]

Hello Ashish,

Are you able to use two different Fields in this function? I need to remove blanks in Field1 & Field2.

Thanks for your help!

[Ashish_Mathur]

Hi,

Subtract another COUNTBLANK() with the other field

[Vvelarde · Accepted solution]

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

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