Forum Discussion

sevens0001's avatar
sevens0001
Frequent Visitor
5 years ago

Countifs in Power BI

Hi, everyone!    I'm trying to convert a countifs formula from an excel report into Power BI. It has two date context filters and I'm having a hard time wrapping my head around the logic and syntax...
Need Help
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
5 years ago

sevens0001 

you can try this

Open Reqs = CALCULATE(COUNTROWS('Table'),FILTER('Table',('Table'[Open Date]<=max('date'[Date])&&ISBLANK('Table'[cutoff date]))||'Table'[Open Date]<=max('date'[Date])&&'Table'[cutoff date]>max('date'[Date])))

median = CALCULATE(AVERAGE('Table'[Days Open]),FILTER('Table',('Table'[Open Date]<=max('date'[Date])&&ISBLANK('Table'[cutoff date]))||'Table'[Open Date]<=max('date'[Date])&&'Table'[cutoff date]>max('date'[Date])))

I am not sure why the median for july and august is different from your expected output.

pls see the attachment below

sevens0001's avatar
sevens0001
Frequent Visitor
5 years ago

This actually produced the same result as my original metric! I feel like that is a good sign, but I'm going to wait and hold off on marking it as a solution until I test something else. Thank you!