Reply
Frequent Visitor
Posts: 4
Registered: ‎11-22-2016
Accepted Solution

count numeric column cells and exclude certain value

I would like to count the number of cells in a numeric column but exclude if the value is 0.  Seems like this should be fairly easy, but since I am new to PowerBI, I would appreciate any assistance!  Thank you!


Accepted Solutions
Established Member
Posts: 150
Registered: ‎11-03-2016

Re: count numeric column cells and exclude certain value

Hey @dlgardo,

 

Sean's answer works for me. This also seems to work:

 

Count Column = COUNTX(FILTER('Table', 'Table'['Column'] <> 0),[Column])

@Sean can you confirm?

 

Thanks,

 

Alan

View solution in original post

Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: count numeric column cells and exclude certain value

[ Edited ]

Yep that's another way of doing the same thing...

 

the only thing I'll change is add the table name as per Rob Collie's post here

 

EDIT: Sorry the post was actually by @MattAllington on Rob's site

 

  • Column reference:  Table[Column Name]
  • Measure Reference:  [measure name]

But that's just a suggestion it should work regardless Smiley Happy

 

Count Column = COUNTX ( FILTER ('Table', 'Table'[Column] <> 0) , 'Table'[Column] )

 

View solution in original post


All Replies
Established Member
Posts: 150
Registered: ‎11-03-2016

Re: count numeric column cells and exclude certain value

Hey @dlgardo,

 

Create a new column like the one below:

 

Count Column = SUMX([Dataset], IF([Column1]<>0, 1, 0))

 

This will count all the fields that are not equal to 0.

 

Hope this helps,

 

Alan

Frequent Visitor
Posts: 4
Registered: ‎11-22-2016

Re: count numeric column cells and exclude certain value

Thank you very much for the reply!  Seems like that is counting every cell in the table.  I just need a count of rows based on a column if the value is not 0.  I have a total of about 86000 rows, and the number of rows that do not have a 0 value in Column F should come down to approx. 24K.

Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: count numeric column cells and exclude certain value

[ Edited ]

Here's an alternative

 

Column Count Measure = CALCULATE ( COUNTROWS('Table'), 'Table'[Column] <> 0 )

The above is basically internally treated like this...

 

Column Count Measure = CALCULATE ( COUNTROWS('Table'), FILTER ( 'Table', 'Table'[Column] <> 0 ) )

Also if for some reason you neeed to exclude the zeros but include the blanks just add the countblank like this

 

Column Count Measure = CALCULATE ( COUNTROWS('Table'), 'Table'[Column] <> 0 ) + COUNTBLANK ( 'Table'[Column] )

 

Established Member
Posts: 150
Registered: ‎11-03-2016

Re: count numeric column cells and exclude certain value

Hey @dlgardo,

 

Sean's answer works for me. This also seems to work:

 

Count Column = COUNTX(FILTER('Table', 'Table'['Column'] <> 0),[Column])

@Sean can you confirm?

 

Thanks,

 

Alan

Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: count numeric column cells and exclude certain value

[ Edited ]

Yep that's another way of doing the same thing...

 

the only thing I'll change is add the table name as per Rob Collie's post here

 

EDIT: Sorry the post was actually by @MattAllington on Rob's site

 

  • Column reference:  Table[Column Name]
  • Measure Reference:  [measure name]

But that's just a suggestion it should work regardless Smiley Happy

 

Count Column = COUNTX ( FILTER ('Table', 'Table'[Column] <> 0) , 'Table'[Column] )