Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Display count of waiting cases on a given date

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_idinserting_dateopening_dateclosing_date
1232019-07-122019-07-172019-07-18
4562019-07-142019-07-192019-07-19
7892019-07-152019-07-232019-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

 

2 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

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.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

Hi @Anonymous 

All the steps are in the Days table.

  1. Copy ExpandedDates function to your Model.
  2. Reference 'Table' table and call it Days
  3. Invoke Custom Function as below.image.png
  4. Select case_id and ExpandedDates columns and Right Click Remove other columns.
  5. Expand to new Rows "Expand Date" Column
  6. Change Data Type of ExpandDates column to Date, then close and apply.
  7. Create relationships between Case, Dates tables and Days table.

Hope this helps, let me know how you get on.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

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.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

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

Capture.PNG"

Hi @Anonymous 

All the steps are in the Days table.

  1. Copy ExpandedDates function to your Model.
  2. Reference 'Table' table and call it Days
  3. Invoke Custom Function as below.image.png
  4. Select case_id and ExpandedDates columns and Right Click Remove other columns.
  5. Expand to new Rows "Expand Date" Column
  6. Change Data Type of ExpandDates column to Date, then close and apply.
  7. Create relationships between Case, Dates tables and Days table.

Hope this helps, let me know how you get on.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.