Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Can anybody please help me understand how to make this simple Excel Countif work in Power Query or DAX, please?! 😞
Solved! Go to 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
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