Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
if there is number not 0 in clolumn it should count 1 from a to c as example below. If all columns values >0 then it should count 1.
I want that result at the end. In excel I used =3- Countif(a1:c1, "0")
A B C RESULT
2.1 8.9 6 3
3.0 0 0 1
0 2.5 8 2
0 0 0 0
Solved! Go to Solution.
Measure = COUNTX ( FILTER ( TableName, TableName[A] > 0 ), TableName[A] )
+ COUNTX ( FILTER ( TableName, TableName[B] > 0 ), TableName[B] )
+ COUNTX ( FILTER ( TableName, TableName[C] > 0 ), TableName[C] ) + 0
Proud to be a Super User!
A more elegant solution in Power Query, especially there are more columns,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIzVNJRstCzBJJmQGwA5hkqxepEKxlD+SBsCsSWYFEQz0gPxLcAs4zAKmAyyBhoSiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t]),
#"Added Custom" = Table.AddColumn(Source, "Result", each List.Accumulate(Record.ToList(_),0,(s,c)=>if c<>"0" then s+1 else s))
in
#"Added Custom"
BTW, here's a concise Excel formula,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks everyone for helping me out, really appreciated. 😊
A more elegant solution in Power Query, especially there are more columns,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIzVNJRstCzBJJmQGwA5hkqxepEKxlD+SBsCsSWYFEQz0gPxLcAs4zAKmAyyBhoSiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t]),
#"Added Custom" = Table.AddColumn(Source, "Result", each List.Accumulate(Record.ToList(_),0,(s,c)=>if c<>"0" then s+1 else s))
in
#"Added Custom"
BTW, here's a concise Excel formula,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
where should I put this code in blank query? Can we make measure or add column in edit query ?
Hi @adnankabina !
You can place this in Power Query, new blank query. The DAX code which i provided can be using in New Column.
Regards,
Hasham
Measure = COUNTX ( FILTER ( TableName, TableName[A] > 0 ), TableName[A] )
+ COUNTX ( FILTER ( TableName, TableName[B] > 0 ), TableName[B] )
+ COUNTX ( FILTER ( TableName, TableName[C] > 0 ), TableName[C] ) + 0
Proud to be a Super User!
Hi @adnankabina !
You can using following DAX to create new column;
AllColumn =
VAR _Col1 = IF(Table[A] > 0, 1, 0)
VAR _Col2 = IF(Table[B] > 0, 1, 0)
VAR _Col3 = IF(Table[C] > 0, 1, 0)
RETURN
_Col1 + _Col2 + _Col3
Hope this wil solve your issue.
Regards,
Hasham
User | Count |
---|---|
120 | |
66 | |
66 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |