cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
goaltender36 Regular Visitor
Regular Visitor

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       1    0   1   0  0 

Row3      0    0    0   0   0

 

How can I create a formula that will look at Row1 and then the first value it detects, it counts it as "1" and then moves onto Row2 until it detects a value, and so on and so forth. 

 

In lamest terms; I go to Row1, column A nothing so I go to Column B, found something in Row1, now i will go to Row2, first try column A, now off to Row3 nothing in all 5 columns so 0

 

End goal: going off the example above; I should see a value of 2 since only 2 rows contain any data. 

 

How can I do this in Power BI???

 

Thank you 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Countif for multiple rows

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

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Super User
Super User

Re: Countif for multiple rows

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

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Moderator v-caliao-msft
Moderator

Re: Countif for multiple rows

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 Regular Visitor
Regular Visitor

Re: Countif for multiple rows

Thank you! I think it worked! It gave me the number of rows that had a value in them

Highlighted
goaltender36 Regular Visitor
Regular Visitor

Re: Countif for multiple rows

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