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.
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 |
---|---|
82 | |
79 | |
66 | |
49 | |
46 |
User | Count |
---|---|
103 | |
44 | |
39 | |
39 | |
39 |