Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |