how to use countif accross each columns

[adnankabina]

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

[ChrisMendoza · Accepted solution]

@adnankabina 

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

[adnankabina]

Thanks everyone for helping me out, really appreciated. 😊

[CNENFRNL · Accepted solution]

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,

[adnankabina]

@CNENFRNL

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

[HashamNiaz]

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

[ChrisMendoza · Accepted solution]

@adnankabina 

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

[HashamNiaz]

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