Power BI Table Refresh Dynamics
Unlike imported tables, calculated tables get recomputed whenever there is a refresh of the dataset, no matter whether the data at the source has changed or not.
If the calculated tables depend on large datasets or computations in more than one DAX expression, then they will considerably delay refresh time. First-and-Foremost DAX Functions
Using SUMMARIZE, ADD COLUMNS, or CROSS JOIN functions in the calculated table defines processing time, particularly for datasets of greater sizes. Other factors that may hurt performance include complex relationships and extensive use of computed columns.
Flying in the Face of Dependencies
Any calculated table that is directly dependent on another will cause the sequential operation of Power BI, thus elongating the refresh interval considerably. Circular dependency between tables means flimsy performance because unnecessary recalculation begins.
Some Optimization Suggestions for Calculated Tables-The-Refresh
Pre-calculation in Power Query or SQL
Instead of using the DAX calculated tables, move the transformation to Power Query (M language) or SQL views.
Materialize pre-aggregated tables in the source database instead of computing them in Power BI.
Direct Relationship over Calculated Table
If the only purpose of a calculated table is to create relationships (like a bridge table), consider whether you can instead use a direct relationship.
Instead of creating lookup tables dynamically in DAX, consider using dimension tables.
Avoid Complex DAX Functions
Avoid SUMMARIZE to create aggregating tables; use Group By in Power Query instead.
Use SELECTCOLUMNS when extracting columns instead of ADDCOLUMNS because it is more efficient.
Reduce Dependencies
Never chain one calculated table onto another.
When possible, break larger calculated tables into several smaller, simpler tables.
Refresh performance can be enhanced greatly within Power BI reports by reductions in DAX complexity, pre-processing data in Power Query or SQL, and decreased dependencies.
