Forum Discussion

goaltender36's avatar
goaltender36
Helper I
10 years ago
Solved

Countif for multiple rows

Hello,   I am trying to count if there is a valuse in a row. My table has 5 columns some rows have a value and some dont example   column   A   B  C  D  E Row1      0    1   0   0  0  Row2     ...
Need Help
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    hi goaltender36

     

    CountVal =
SUMX (
    'Table-Count',
    IF (
        'Table-Count'[A] + 'Table-Count'[B]
            + 'Table-Count'[C]
            + 'Table-Count'[D]
            + 'Table-Count'[E]
            > 0,
        1,
        0
    )
)

     

     

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
10 years ago

Hi goaltender36,

 

DAX has a function blank(), which could be used as blank value. And if 0 would be considered as empty value, then the formula would be a little difference.

 

Create the count measure in the following format when the value is considered as blank:
CountAX = SUMX(Sheet1, if(Sheet1[A]<>blank()||Sheet1[B]<>blank()||Sheet1[C]<>blank()||Sheet1[D]<>blank()||Sheet1[E]<>blank(), 1, 0))

 

For value of 0, check the measure below:
CountX3 = SUMX(Sheet1, if(Sheet1[A]<>0||Sheet1[B]<>0||Sheet1[C]<>0||Sheet1[D]<>0||Sheet1[E]<>0, 1, 0))

 

If any further questions, please feel free to post back.

 

Regards,

Charlie Liao

  • goaltender36's avatar
    goaltender36
    Helper I
    10 years ago

    I will try this solution and then add it to the table. Thank you!!!