Forum Discussion
Improve speed in occupancy calculation
Based on hospitalizations (startdatetime, enddatetime, department, specialty), I am calculating the occupancy per (date) time unit:
1. create a list of time units (hours) between the enddatetime and startdatetime
Table.AddColumn(#"Add date/time info", "DateTime", each List.DateTimes([AUX_StartHour], ([AUX_EndHour] - [AUX_StartHour]) / TIME_UNIT, TIME_UNIT))
2. expand this list
Table.TransformColumnTypes(Table.ExpandListColumn(#"CREATE TIMEUNIT list between start/end datetime", "DateTime"), {{"DateTime", type datetime}})
3. compute the occupancy per timeunit
Table.AddColumn(#"EXPAND DateTime", "NrOcc", each
if [StartDateTime] - [DateTime] >= TIME_UNIT then 0 // ignore timestamps before startmoment
else if [DateTime] >= [EndDateTime] then 0 // ignore timestamps after endmoment
else if [StartDateTime] > [DateTime] and [StartDateTime] - [DateTime] < TIME_UNIT then ([DateTime] + TIME_UNIT - [StartDateTime]) / TIME_UNIT // correct when starting moment falls within time unit
else if [EndDateTime] > [DateTime] and [EndDateTime] < [DateTime] + TIME_UNIT then ([EndDateTime] - [DateTime]) / TIME_UNIT // correct when ending moment falls within time unit
else 1, type number)
4. remove redundant rows
Table.SelectRows(#"Compute Occupancy per time unit", each ([NrOcc] <> 0))
5. Summarize occupancy by grouping:
Table.Group(#"Removed Other Columns", {"DateTime", "Department"}, {{"NrOcc", each List.Sum([NrOcc]), type number}})
Especially the last step is taking very (too) long locally and in power bi service I even get a memory error. Does someone know how to improve this query? Or how to fix the memory error in pbi service?
Thanks in advance!!
Hi Anonymous,
You can create a separate calendar table that lists every hour in the period you want to analyze. This table is much smaller than expanding all hospitalizations into hourly rows. Then, for each hour in the calendar, you look up and match only the hospitalizations where the patient was actually present during that hour, that is, where the hospitalization start time is before or equal to the hour and the end time is after it. This avoids unnecessary expansion and keeps the dataset manageable. Once you have matched the hospitalizations to each hour, you can expand and group the data by hour and department, counting how many hospitalizations overlap each hour to calculate occupancy. This method is flexible, allowing you to adjust the time period and increment (like hourly or half hourly), and it dramatically reduces the number of rows and memory load compared to expanding every hospitalization for its full duration. Filtering the calendar to the exact range needed and using techniques like buffering can further improve performance.
Please refer to below similar thread solved in community and let me know if this helps?
Solved: Calculating hourly occupancy of a medical clinic (... - Microsoft Fabric Community
Thanks,
prashanth
MS fabric community support
9 Replies
Hi Anonymous
The main reason your query is running slow is because you’re expanding a full list of time units between start and end for every record. That creates a huge intermediate table, which eats memory and causes slow refresh, especially in the service. Instead of generating all rows, try using DAX measures or aggregations that calculate occupancy based on start and end without expanding the full list. Another option is to pre-aggregate data in Power Query or at the database level before loading into Power BI. If you must stay in Power Query, avoid List.Dates expansion and work with duration calculations directly - this way you only store start and end, and compute overlaps on the fly. In short, reducing row expansion and pushing heavy logic upstream (SQL or staging table) is the best way to fix both speed and memory errors.
Hi Rohit1991,
Thanks for your response! I need to stay in Power Query indeed. Could you elaborate a bit on the suggestions you give? So what do you exactly mean with "avoid List.Dates expansion and work with duration calculations" Could you give an example?
And with "Another option is to pre-aggregate data in Power Query or at the database level before loading into Power BI" (data is imported from files on teams)?
Thanks!
Fleur
Hi Anonymous
-
Instead of expanding List.Dates, compute days directly: add a custom column Days = Duration.Days([EndDate] - [StartDate]), then use that number in your occupancy logic (e.g., split by month only if needed, not per day).
-
Pre-aggregate in Power Query: Home >> Group By >> group by Department and Month, aggregate Days with Sum (and counts as needed). This reduces rows before the model loads and speeds everything up.
-
If you can edit the source file, do the same grouping there (Department/Month totals) so Power BI only imports summarized data.
-
Hi Anonymous,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
rohit1991, thanks for your prompt response
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth AreThanks for confirmation, we will try look into it and post here if got working solution for you
Hi Anonymous,
You can create a separate calendar table that lists every hour in the period you want to analyze. This table is much smaller than expanding all hospitalizations into hourly rows. Then, for each hour in the calendar, you look up and match only the hospitalizations where the patient was actually present during that hour, that is, where the hospitalization start time is before or equal to the hour and the end time is after it. This avoids unnecessary expansion and keeps the dataset manageable. Once you have matched the hospitalizations to each hour, you can expand and group the data by hour and department, counting how many hospitalizations overlap each hour to calculate occupancy. This method is flexible, allowing you to adjust the time period and increment (like hourly or half hourly), and it dramatically reduces the number of rows and memory load compared to expanding every hospitalization for its full duration. Filtering the calendar to the exact range needed and using techniques like buffering can further improve performance.
Please refer to below similar thread solved in community and let me know if this helps?
Solved: Calculating hourly occupancy of a medical clinic (... - Microsoft Fabric Community
Thanks,
prashanth
MS fabric community support
Hi Anonymous, We would like to confirm did get chance to try my above solution and helps? If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
Can you confirm once are you able to resolve your issue? If you find solution or your query got resolved. Can you please share the resolution steps here. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.
