Conditional count values across mutliple columns

[Anonymous]

I'm trying to count the number of values that are not zero nor null across multiple colums.

I think it has to CALCULATE(COUNTCOLUMNS and a conditional.  But I can't figure the coding.

[Anonymous]

I couldn't get it to work.  My co-worker suggested the coding below but the first line is giving me a error :  SyntaxError : Token ')' expected.   Does anyone have any suggestions?

#"SpecificColumns" = (#"Changed Type1",{"Database.EIA - 2024-2025", "Database.EIA - 2025-2026", "Database.EIA - 2026-2027", "Database.EIA - 2027-2028"}),
#"CountGreaterThanZero" = List.Count(List.Select(#"SpecificColumns", each _ > 0))
in
#"CountGreaterThanZero"

[Anonymous]

Above is a sample of the actual data.  Also I replaced the null values with zero.  I haven't been able to try your solution.  Been sidetracked with other projects.  Will try on the weekend.

[ronrsnfld]

This is simple to do in Power Query.

After your step that produces the table you show, add this code which will create a column names "Counts".

If there are other columns than the ones you show, use something like List.Range to narrow it down.

  #"Add Counts" = Table.AddColumn(#"Previous Step","Count", 
        (r)=>List.Count(List.RemoveMatchingItems(Record.FieldValues(r),{"",0,null})),Int64.Type)

[Anonymous]

Hi @Anonymous ,

Please provide some sample data and your expected results, thank you!

Here I build some sample data for testing:

And you can use these DAXs:

CountRows_Value1 = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Value1] <> BLANK() && 'Table'[Value1] <> 0
)

CountRows_Value1 & Value2 = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Value1] <> BLANK() && 'Table'[Value1] <> 0 && 'Table'[Value2] <> BLANK() && 'Table'[Value2] <> 0
)

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.