Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I am not sure how to filter or eventually create a function for this problem. I'm aware of this thread ( https://community.powerbi.com/t5/Desktop/Count-of-Open-cases-over-time/m-p/761764#M367099 ), but it doesn't seem to be the most efficient way. Especally the part about duplicating Cases since my cases table has 700 000 rows.
I have the following data: Cases(case_ID, inserting_date, opening_date, closing_date). The dates are between 2016 and today.
case_id | inserting_date | opening_date | closing_date |
123 | 2019-07-12 | 2019-07-17 | 2019-07-18 |
456 | 2019-07-14 | 2019-07-19 | 2019-07-19 |
789 | 2019-07-15 | 2019-07-23 | 2019-07-25 |
I would like to know the count of cases that were "waiting" on a given date. A case is waiting if it is inserted but not opened.
For example, the count should be like following:
2019-07-12 count 1
2019-07-14 count 2
2019-07-16 count 3
2019-07-22 count 1
Solved! Go to Solution.
Hi @Anonymous
Power bi was built for aggregations so expanding your Cases table should not be a problem if done with care, in the attache file you will find an example of how to do it using the Query Editor.
Hi @Anonymous
All the steps are in the Days table.
Hope this helps, let me know how you get on.
Hi @Anonymous
Power bi was built for aggregations so expanding your Cases table should not be a problem if done with care, in the attache file you will find an example of how to do it using the Query Editor.
Thank you @Mariusz , This was exactly what I have been looking for. I'm quite new to Power Bi and can't really figure out how you created this, what I suppose is an "Invoke Custom Function
"
Hi @Anonymous
All the steps are in the Days table.
Hope this helps, let me know how you get on.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |