# Counting Columns with Specific Conditions

• ### Question

• Good Day!

Need some help...

I want to count how many of my columns have zero entries for the entire duration of a specific (sliced) time frame (row)...

example:

Month | A | B | C | D | E | F | G | H | I | J | etc...

Jan         2   4   5   7   1   3   2    1   9   1

Feb        3   0   4   4   0    1   0    0   4   0

Mar        1   0   3  4    0    0   0   0   2   0

Apr        2   0   1  3    0    0   0   0   3   0

May       2   0   1  2    0    0   0   0    1   0

Jun        1   1   2  0    1   1    2   0    2   0

if i wanted to count how many of my columns have "zero" entries from feb to may (answer=5), how would I go about that?

Tuesday, December 17, 2019 8:20 AM

### All replies

• if i wanted to count how many of my columns have "zero" entries from feb to may (answer=5), how would I go about that?

So you cannot count a variable number of columns. To count a fixed list of columns with 0 you could do something like the following:

SUMX( 'Table',  IF( 'Table'[A] = 0 || 'Table'[B] = 0 || 'Table'[C] = 0 || etc...   , 1 ) )

But you also have the issue that BLANK values are numerically equal to 0 so if you need to exclude blanks it becomes

SUMX( 'Table',  IF( ('Table'[A] = 0 && NOT(ISBLANK('Table'[A] ))
|| ('Table'[B] = 0 && NOT(ISBLANK('Table'[B] ))
|| ('Table'[C] = 0 && NOT(ISBLANK('Table'[C] ))
|| etc...
, 1 ) )

But the fact that you have etc.. on the column also suggests that possibly you could consider unpivoting the data so that instead of lots of columns you just have 3.

eg.

Month Column Value
Jan      A          2
Jan      B          4
Jan      C          5
etc...

Then the entire measure would become something like the following:

CALCULATE( DISTINCTCOUNT( 'table'[Column] ), 'Table'[Value] =0 && NOT(ISBLANK( 'Table'[Column] )) )