cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## 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
Resident Rockstar
``````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!

Community Champion

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

Thanks everyone for helping me out, really appreciated. 😊

Community Champion

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

### @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

Resident Rockstar
``````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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors