Improving performance for large data sets

Here are the most effective strategies to significantly improve your dashboard's speed.

1. Implement a Star Schema Data Model
This is the single most important optimization you can make. Instead of having one large, wide table, you should structure your data into a star schema.

What it is: A central Fact Table (containing your numeric transaction data like Sales Amount, Quantity, and ID keys) surrounded by smaller Dimension Tables (containing descriptive attributes like User Details, Product Info, Calendar Dates, etc.).

Why it's faster:

Reduces Memory: The Power BI engine is highly optimized for this structure. It stores the repetitive text values from dimensions only once, dramatically reducing the file size and memory usage.

Faster Relationships: Queries become much more efficient as they operate on smaller dimension tables and leaner fact tables.

Action:

Identify repeating attribute columns in your main table (e.g., User Name, User Department, Product Category).

Create new tables for each distinct entity (e.g., a 'Users' table, a 'Products' table). Use Power Query to remove duplicates.

Replace the text columns in your fact table with integer ID keys that relate back to these new dimension tables.

Create a dedicated Calendar Table for all your date-based calculations instead of using Power BI's auto date/time.

2. Use Aggregation Tables
This is the most powerful technique for your specific scenario (aggregating daily data to weekly/monthly views). Aggregations allow Power BI to use a much smaller, pre-summarized table for high-level visuals and only query the massive 13-million-row detail table when a user drills down.

Action:

In Power Query, create a new query that references your main transaction table.

Use the Group By function to aggregate your data to the level of your visuals (e.g., group by Month, User ID, Product ID and sum the sales). This creates your aggregation table.

Load both the original detail table and this new aggregation table into your model.

In the Model view, right-click the aggregation table and select Manage aggregations. Map the summarized columns in your aggregation table to the corresponding columns in your detail table.

Power BI will now automatically use the small, fast aggregation table for monthly/weekly charts and seamlessly switch to the detail table only when necessary.

3. Write More Efficient DAX
Inefficient DAX measures can be a major bottleneck.

Use Variables (VAR): Always use variables to store calculations that are used multiple times within a single measure. This ensures the calculation is performed only once, not repeatedly.

Avoid Filtering Whole Tables: Instead of writing FILTER('YourTable', ...) use FILTER(ALL('YourTable'[Column]), ...) or KEEPFILTERS(). Filtering specific columns is much faster than filtering entire tables.

Be Careful with Iterators (X functions): Functions like SUMX and FILTER can be slow if they have to iterate over all 13 million rows. Try to perform calculations inside a CALCULATE filter context whenever possible, as this is more efficient.

4. Optimize the Report Page
Finally, analyze what's happening on the report page itself.

Use the Performance Analyzer: Go to the View tab and open the Performance Analyzer. Click "Start recording" and then refresh your visuals. It will show you exactly how many milliseconds each visual takes to load and which part (DAX Query, Visual Display) is the slowest. This helps you pinpoint the exact bottleneck.

Reduce Visuals: Every visual on a page sends at least one query. The fewer visuals you have, the faster the page will load.

Limit High-Cardinality Fields: Avoid using fields with thousands of unique values (like Transaction ID) in slicers or table visuals. This forces Power BI to load and render a huge amount of data.

Edit Interactions: Reduce unnecessary cross-filtering. By default, every visual filters every other visual. Go to Format > Edit interactions to turn off filtering for visuals that don't need to be linked, preventing a cascade of queries every time a user clicks.

If this explanation and solution resolve your issue, please like and accept the solution.

Hi maziiw,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.