Forum Discussion
Incremental refresh with polling expression
Go with the timestamp that has the most even distribution (likely the created date). Create a large enough "hot" window to cover most of the jitter (modifications happening soon after creation). For example make your "hot" partitions the last three months instead of the last month. Then use the Modified date to catch any entries that fall outside the "hot" window and manually refresh their partitions if needed. Plan on doing a full refresh (or a sequential manual refresh of all partitions) every now and then.
No, based on what you've said the problem is with the performance of the SQL queries themselves and nothing to do with Power BI. You need to get a DBA or someone familiar with SQL performance tuning to help you here.
Thanks again lbendlin and cpwebb .
Does implementing the same logic of calculated columns in Power Query, which requires merging tables and some transformations, affect memory and performance again?
cpwebb Yes, the error message is: Resource Governing: This operation was canceled because there wasn't enough memory to finish running it. Either reduce the memory footprint of your dataset by doing things such as limiting the amount of imported data, or if using Power BI Premium, increase the memory of the Premium capacity where this dataset is hosted. More details: consumed memory 3464 MB, memory limit 3464 MB, database size before command execution 1655 MB.
We are on premium Embedded with A2 capacity (5 GB memory).
In our largest table (the table causing the issue), we have 56 columns, comprising 15 native columns, 20 calculated columns using only the 'RELATED' function, and 21 other calculated columns.
I randomly removed 7 columns, those that appeared more complex and that resolved the issue.
These are some of the columns I removed:
It looks like the way you're using the FILTER function to filter entire tables in CALCULATE could be the problem. See https://xxlbi.com/blog/power-bi-antipatterns-9/