Forum Discussion
Helper IVCannot Figure out how to replicate my Excel CountIFS formula
I have the following formula simplified from Excel; I cannot figure out how to replicate this in PowerBI. Any help would be appreciated. All the data is in one table in Power BI. The date qualifications in Excel are sitting in a single cell i.e., A1 = 01/01/2020 ---- FINISH Date < EDATE(A1,1)
=COUNTIFS(
'Sheet 1' FINISH Date >= Jan 2020,
'Sheet 1' FINISH Date < Feb 2020,
'Sheet 1' Actual Finish >= Jan 2020,
'Sheet 1' Actual Finish < Feb 2020,
'Sheet 1' System = "xxx",
'Sheet 1' Type <> "xxxxxxxx")
Thank you
Hi CaveOfWonders ,
Try this measure.Measure = CALCULATE( COUNTROWS(Sheet1), FILTER( Sheet1, Sheet1[Finish date] >= DATE(2020, 1, 1) && Sheet1[Finish date] < DATE(2020, 2, 1) && Sheet1[Actual Finish] >= DATE(2020, 1, 1) && Sheet1[Actual Finish] < DATE(2020, 2, 1) && Sheet1[System] = "x" && Sheet1[Type] = "y" ) )Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- Greg_Deckler
Community Champion
So in general you replace COUNTIF(S) with a structure like:
COUNTX(FILTER(...),...) or CALCULATE(COUNT(...),FILTER(...))
But, can't really tell what your formula is doing. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
- CaveOfWonders
Thanks Greg,
Hi Greg, thank you for your reply. I'm trying to count, only if:
1 - the date in the Finish Dates Column falls between the 1st and 31st Jan
2 - the date in the Actual Finish Column falls between the 1st and 31st Jan
3 - The System column = "x"
4 - The Type column = "y"
I have tried to create the measure but I'm getting the error (The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.)
Measure =CALCULATE (COUNT ( 'Sheet 1'[FINISH Date] ),('Sheet 1'[FINISH Date] >= DATE ( 2020, 1, 1 )&& 'Sheet 1'[FINISH Date] < DATE ( 2020, 2, 1 ))&& ('Sheet 1'[Actual Finish] >= DATE ( 2020, 1, 1 )&& 'Sheet 1'[Actual Finish] < DATE ( 2020, 2, 1 ))&& ( 'Sheet 1'[System] = "x" )&& ( 'Sheet 1'[Type] = "x" ))
v-lionel-msftCommunity Support
Hi CaveOfWonders ,
Try this measure.Measure = CALCULATE( COUNTROWS(Sheet1), FILTER( Sheet1, Sheet1[Finish date] >= DATE(2020, 1, 1) && Sheet1[Finish date] < DATE(2020, 2, 1) && Sheet1[Actual Finish] >= DATE(2020, 1, 1) && Sheet1[Actual Finish] < DATE(2020, 2, 1) && Sheet1[System] = "x" && Sheet1[Type] = "y" ) )Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
