Best Practices for Optimizing Power BI Model with 50+ Million Fact Rows & Multiple Dimensions

[Suresh_]

Hello Power BI Experts,

I’m currently working on a Power BI project that involves a large-scale data model. The main fact table contains over 50 million rows, supported by several dimension tables, including some with slowly changing dimensions (SCDs). As the volume and complexity of the model increase, I’m encountering performance challenges — especially around report responsiveness, DAX execution time, and dataset refresh duration.

I'm seeking guidance on enterprise-level optimization techniques across the full BI pipeline — from data modeling to DAX, refresh performance, and deployment practices.

---

1. Data Modeling Strategy

• Is a star schema still the best choice for such large models?

• How should I handle historical data — should I split fact tables into current vs. archive for performance?

• What’s the ideal approach for handling Type 2 SCDs in Power BI?

---

2. DAX Optimization

• Which DAX patterns (e.g., iterators, CALCULATE with filters) tend to be problematic with high-volume data?

• How can I improve performance of dynamic measures (e.g., rankings, conditional metrics)?

• Any tips for structuring calculation groups or avoiding unnecessary recalculations?

---

3. Power Query & Data Load

• What are best practices for ensuring query folding, especially when working with APIs or complex transformations?

• How do you manage modular and reusable M code for maintainability at scale?

---

4. Memory & Compression

• How important is column cardinality and data type selection for VertiPaq optimization?

• What tools do you recommend for analyzing model size and memory usage (e.g., VertiPaq Analyzer, DAX Studio)?

---

5. Refresh Strategy

• How should I configure incremental refresh for large models, especially when the date field is custom?

• Should I use dataflows or direct table imports for more control over refresh performance?

---

6. Visual Layer Optimization

• What types of visuals or filters tend to reduce report performance?

• How to structure slicers and drill-throughs to avoid heavy backend queries?

---

7. Deployment & Monitoring

• What are your recommended practices for managing Dev, Test, and Production environments?

• Any suggestions for CI/CD setup, version control, or monitoring large models in Power BI Service?

---

Final Request:

If you’ve managed Power BI models at this scale (50M+ rows), I’d love to hear:

• Your architecture/design strategies

• Optimization tips you’ve implemented successfully

• Tools and techniques you rely on to keep the solution maintainable and performant

Any detailed blog links, Microsoft documentation, or real-world case studies would be appreciated as well.

Thank you in advance,
Suresh

[v-dineshya]

Hi @Suresh_ ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @Greg_Deckler , Thank you for your prompt response.

Hi @Suresh_ , Could you please try the proposed solution shared by @Greg_Deckler . Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @Suresh_ ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @Suresh_ ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @Suresh_ ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Greg_Deckler · Accepted solution]

@Suresh_ There's a lot here:

1. Data Modeling Strategy

• Is a star schema still the best choice for such large models?

Generally yes

• How should I handle historical data — should I split fact tables into current vs. archive for performance?

Often people aggregate historical data at a higher granularity.

• What’s the ideal approach for handling Type 2 SCDs in Power BI?

Same approach as is general for handling Type 2 SCDs

---

2. DAX Optimization

• Which DAX patterns (e.g., iterators, CALCULATE with filters) tend to be problematic with high-volume data?

https://medium.com/@gdeckler/performance-tuning-dax-part-1-0a60ee8ca601

My book in my signature has an entire chapter on optimizing DAX and there is a huge book, Optimizing DAX available from sqlbi

• How can I improve performance of dynamic measures (e.g., rankings, conditional metrics)?

See above

• Any tips for structuring calculation groups or avoiding unnecessary recalculations?

See above

---

3. Power Query & Data Load

• What are best practices for ensuring query folding, especially when working with APIs or complex transformations?

Check out gorillabi by Rick de Groot. Chandeep's M book as well as The Definitive Guide to Power Query M are good choices here.

• How do you manage modular and reusable M code for maintainability at scale?

See above

---

4. Memory & Compression

• How important is column cardinality and data type selection for VertiPaq optimization?

Very

• What tools do you recommend for analyzing model size and memory usage (e.g., VertiPaq Analyzer, DAX Studio)?

Both of those, Tabular Editor as well 

---

5. Refresh Strategy

• How should I configure incremental refresh for large models, especially when the date field is custom?

Need more information

• Should I use dataflows or direct table imports for more control over refresh performance?

Maybe dataflows. For something of this scale, this might be a good application of Databricks, Snowflake, or Fabric to process the data and get it into a data warehouse and then build the semantic model from that.

---

6. Visual Layer Optimization

• What types of visuals or filters tend to reduce report performance?

This is generally more about how many visuals on a page versus type of visual. There are some performance features such as "Apply filters" that you can use to help improve performance sometimes.

• How to structure slicers and drill-throughs to avoid heavy backend queries?

See above

---

7. Deployment & Monitoring

• What are your recommended practices for managing Dev, Test, and Production environments?

Different workspaces for each, use deployment pipelines.

• Any suggestions for CI/CD setup, version control, or monitoring large models in Power BI Service?

Github integration