Forum Discussion
Improve speed in occupancy calculation
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
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.
-