Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to Solution.
CountVal = SUMX ( 'Table-Count', IF ( 'Table-Count'[A] + 'Table-Count'[B] + 'Table-Count'[C] + 'Table-Count'[D] + 'Table-Count'[E] > 0, 1, 0 ) )
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!!!
CountVal = SUMX ( 'Table-Count', IF ( 'Table-Count'[A] + 'Table-Count'[B] + 'Table-Count'[C] + 'Table-Count'[D] + 'Table-Count'[E] > 0, 1, 0 ) )
Thank you! I think it worked! It gave me the number of rows that had a value in them
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
75 | |
54 | |
38 | |
31 |
User | Count |
---|---|
99 | |
56 | |
50 | |
42 | |
40 |