Optimizing the right table is the fastest way to improve report performance. In real projects, not every table slows down your model, only a few heavy hitters do. Here are the practical ways to quickly identify them.

1. Use Performance Analyzer (Power BI Desktop)

Performance Analyzer tells you which visuals take the longest time and indirectly reveals which tables are the bottleneck.

Steps to check:

• Look for visuals with unusually high DAX Query time

• Identify which tables those visuals depend on

• If one visual repeatedly appears in the top list → trace it back to the underlying table

• Check if heavy calculations (SUMX, FILTER, DISTINCT) are targeting large tables

2. Inspect Table Size in Model View

Large tables cause slow refresh, slow DAX, and high memory usage.

Steps to check:

• Sort tables by Row Count

• Look for dimension tables with suspiciously high rows (should be small)

• Identify fact tables having unnecessary columns

• Detect wide tables (too many columns = slower compression)

3. Analyze Column Cardinality

High cardinality values slow down relationships and aggregations.

Steps to check:

• Columns with too many unique values (e.g., long IDs, timestamps)

• DateTime columns with seconds/milliseconds

• Text columns storing unique strings

• Columns used in relationships or slicers with high cardinality → top priority to optimize

4. Review Power Query Steps

Some tables become slow because of complex transformations.

Steps to check:

• Applied steps with non-folding operations

• Merges or appends done on large tables

• Adding calculated columns in Power Query instead of source

• Too many custom columns or nested conditions

5. Check DAX Dependencies

Some tables become bottlenecks because DAX relies heavily on them.

Steps to check:

• Measures repeatedly scanning the same table

• SUMX / FILTER / ADDCOLUMNS that loop over large tables

• Measures reading columns that aren’t used in visuals

• Calculated tables depending on large base tables

6. Use VertiPaq Analyzer or DAX Studio

These tools show true storage cost inside the model.

Steps to check:

• Largest tables by memory

• Columns with highest storage (blow-up columns)

• Columns with poor compression

• Identify columns that can be removed or split

7. Monitor Refresh Performance

Refresh duration tells you which table slows down the pipeline.

Steps to check:

• Tables taking significantly longer to load

• Long-running joins/merges

• Tables where query folding breaks mid-way

• Imported tables pulling unnecessary data from the source

8. Identify Business-Driven Heavy Tables

In real-world models, some tables are naturally heavy.

Steps to check:

• Transaction tables (invoices, logs, events, sales)

• Daily snapshots or fact tables with incremental data

• Tables with large historical data that aren’t required

• Fact tables that are not filtered by date or category