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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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