Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
goaltender36
Helper I
Helper I

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
Vvelarde
Community Champion
Community Champion

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

View solution in original post

4 REPLIES 4
v-caliao-msft
Microsoft Employee
Microsoft Employee

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

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

Vvelarde
Community Champion
Community Champion

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

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.