locked
Counting Columns with Specific Conditions RRS feed

  • 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?

    please help... thank you...

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


    http://darren.gosbell.com - please mark correct answers

    Tuesday, December 17, 2019 9:57 PM