Power BI Duplicating data in tables or referencing other tables in calculations

[PJStevens]

Hi,

I am working for a water utility company that uses Power BI to visualize data and get KPI's for monthly and yearly reports. We measure tons of data and this causes our dashboards (especially our main dashboard) to be incredibly big, with lots of calculations and this causes refreshes to be quite slow. What are some ways I can optimize this so that it runs faster and can handle the large quantities of data better? I noticed from the person who set the dashboard up, that he duplicates data a few times to create calculations (for example, he will import data from table A into table B then do a calculation in table B based on the data from table A). Would it be better to just straight up create the calculation in table B using the data from table A without duplicating the data (just for information, the two dim tables have a relationship to a fact table so they are linked). What are some other ways I can optimize the overall performance of my Power BI refresh and query performance when working with gigantic data sets?

Thanks in advance!

[v-zhangti · Accepted solution]

Hi, @PJStevens 

Here are some strategies to enhance both refresh and query performance:

Optimize the Data Model:

Choose the appropriate semantic model type for your solution: Import, DirectQuery, or Composite. Understand their differences and select the one that best suits your needs. Implement data reduction techniques for Import modeling to minimize data volume. Follow DirectQuery model guidance if you’re using DirectQuery mode in Power BI Desktop. For Composite models, ensure efficient usage of both Import and DirectQuery tables.

Optimization guide for Power BI - Power BI | Microsoft Learn

Visualizations Optimization:

Dashboards:
Understand that Power BI maintains a cache for dashboard tiles (except live report tiles and streaming tiles).
If your semantic model enforces dynamic row-level security (RLS), be aware of performance implications. Pin frequently used visuals to dashboards to take advantage of cached data. Consider dashboards as a first line of defense for consistent performance.

Incremental Refresh:

Use incremental refresh for large dataflows. It enables faster refreshes after the initial load by refreshing only the necessary partitions (e.g., daily, weekly, monthly) or data.

Understand and optimize dataflows refresh - Power BI | Microsoft Learn

Solved: Refresh large datasets on Power BI service - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

It's a big subject.  Solutions may involve data modelling, the complexity of Power Query steps, DAX efficiencies, powerbi settings.

Start by identifying the bottlenecks: Power Query or DAX.

For the latter, benchmark with Vertipaq Analyzer.

Power Query is not optimised for speed but you can look at things like Merge performance, getting rid of fields which are not required. Get your data at the correct granularity for your reports. Possible use of Table.Buffer.

Have a look at Imke's suggestions : https://www.thebiccountant.com/speedperformance-aspects/