cancel
Showing results for
Did you mean:
Helper I

## how to use countif accross each columns

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

2 ACCEPTED SOLUTIONS
Super User
``````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``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

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

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 beyond their comprehension! DAX is simple, but NOT EASY!
6 REPLIES 6
Helper I

Thanks everyone for helping me out, really appreciated. 😊

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

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 beyond their comprehension! DAX is simple, but NOT EASY!
Helper I

### @CNENFRNL

where should I put this code in blank query? Can we make measure or add column in edit query ?

Solution Sage

You can place this in Power Query, new blank query. The DAX code which i provided can be using in New Column.

Regards,

Hasham

Super User
``````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``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Solution Sage

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors