Forum Discussion
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...
Ashish_Mathur
Super User
Super UserHi,
Share data in a form that can be pasted in an MS Excel file.
Ashish, I'm not sure the best way to go about that, but here's some sample data below. Let me know if this doesn't work. Thanks!
| Job ID | Open Date | Cutoff Date | Days Open |
| 100 | 1/1/2021 | 242 | |
| 105 | 2/26/2021 | 5/7/2021 | 70 |
| 107 | 3/1/2021 | 4/13/2021 | 43 |
| 126 | 3/22/2021 | 162 | |
| 127 | 3/22/2021 | 6/14/2021 | 84 |
| 209 | 4/7/2021 | 4/26/2021 | 19 |
| 210 | 4/7/2021 | 5/3/2021 | 26 |
| 211 | 4/7/2021 | 146 | |
| 291 | 5/4/2021 | 8/26/2021 | 114 |
| 292 | 5/4/2021 | 119 | |
| 293 | 5/4/2021 | 8/10/2021 | 98 |
| 294 | 5/4/2021 | 119 | |
| 295 | 5/5/2021 | 118 | |
| 296 | 5/5/2021 | 8/24/2021 | 111 |
| 297 | 5/5/2021 | 7/1/2021 | 57 |
| 333 | 5/10/2021 | 8/2/2021 | 84 |
| 334 | 5/11/2021 | 112 | |
| 335 | 5/11/2021 | 112 | |
| 380 | 7/2/2021 | 60 | |
| 381 | 7/6/2021 | 56 | |
| 497 | 8/6/2021 | 8/17/2021 | 11 |
| 498 | 8/6/2021 | 25 | |
| 499 | 8/7/2021 | 8/10/2021 | 3 |
| 500 | 8/9/2021 | 22 | |
| 501 | 8/9/2021 | 22 | |
| 502 | 8/9/2021 | 22 |
- Ashish_Mathur
Thank you, Ashish -
I was able to follow how you transformed the data. Unfortunantly I can't expand my rows by the months that it's open. Too many other metrics rely on distinct agreggations on other fields in 'Jobs' table. It would also degrade the performance. I have over 4000 rows of data already with 5 other sources pulling in. Thank you though!