How to do 'CountIfs' in Power Query or in DAX!

[DamianKelly]

Can anybody please help me understand how to make this simple Excel Countif work in Power Query or DAX, please?! 😞

[m_dekorte · Accepted solution]

Hi @DamianKelly 

In Power Query, Group By is useful for these types of requirements:

let
    Source = YourData,
    GroupByRows = Table.Group(Source, {"Product"}, {
        {"AllRows", each _, type table [Product=nullable text, Period=nullable text, Supply=nullable number]}, 
        {"SupplyCount", each List.Count(List.Select([Supply], (x)=> Number.From(x??0) >0 )), type nullable number}
    }),
    ExpandAllRows = Table.ExpandTableColumn(GroupByRows, "AllRows", {"Period", "Supply"}, {"Period", "Supply"})
in
    ExpandAllRows

In DAX you could do something like this:

CountIfs = 
VAR curValue = SELECTEDVALUE( 'YourTable'[Product] )
VAR t = 
    FILTER( 
        ALL( 'YourTable'),
        [Product] = curValue &&
        [Supply] >0
    )
RETURN

COUNTROWS( t )

In a table visual, that will get you these results:

I hope this is helpful