Forum Discussion
Performant Power BI Model and Model Subset
First, I do not see any need here for a secondary model with a subset of data.
Import Mode is the default for performance; especially with scheduled refreshes and DAX optimizations and is a great starting choice. But it has memory limits (especially on Pro vs Premium capacity) and can slow down with high cardinality or frequent drilldowns.
In general, its less about the size of the model and more about how the model is constructed.
Make sure you are doing these things:
- Use a star schema
- Reduce column cardinality
- Get rid of unnecessary fields
- Always use explicit measures
Please mark this post as solution if it helps you. Appreciate Kudos.
Hi henry_gonsalves ,
Thank you andrewsommer for the helpful insights!
In addition to the andrewsommer suggestion, since you are working with 40M+ rows and SME users need frequent drill downs, it is worth considering a few additional strategies. Partitioning your fact tables by date and enabling incremental refresh can help reduce memory usage and refresh duration.Your idea of a secondary model filtered to recent data is actually a viable approach when you need faster performance for detail-level analysis. You may keep it aligned with your main model by reusing the same dataflows or Lakehouse views, and cloning the original model as a .pbit file.
If real time access becomes important, Direct Query could work, but with some performance trade-offs, so backend optimization is key. Also, tools like the Monitoring Hub and DAX Studio can help you fine.
Refer the documents here for more inforamtion:
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-templates
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
Hope this resolve your query.If so,consider accepting it as solution.
Regards,
Pallavi.
Hi henry_gonsalves ,
Thank you andrewsommer for the helpful insights!
In addition to the andrewsommer suggestion, since you are working with 40M+ rows and SME users need frequent drill downs, it is worth considering a few additional strategies. Partitioning your fact tables by date and enabling incremental refresh can help reduce memory usage and refresh duration.
Your idea of a secondary model filtered to recent data is actually a viable approach when you need faster performance for detail-level analysis. You may keep it aligned with your main model by reusing the same dataflows or Lakehouse views, and cloning the original model as a .pbit file.
If real time access becomes important, Direct Query could work, but with some performance trade-offs, so backend optimization is key. Also, tools like the Monitoring Hub and DAX Studio can help you fine.
Refer the documents here for more inforamtion:
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-templates
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
Hope this resolve your query.If so,consider accepting it as solution.
Regards,
Pallavi.
Thanks Pallavi. Is there a better way to filter the first dataset down to produce the second? Would using a template as above generate a second dataset?
Hi henry_gonsalves ,
Thank you for the follow-up.I would be happy to assist you!
A .pbit template will create a new dataset when published to the Power BI service, retaining the model structure without data. When you apply a filter (like "Last 12 months") in Power Query, only that subset is loaded as a new dataset. To keep models aligned, reuse the same dataflows or Lakehouse views, apply the filter in Power Query (e.g., = Table.SelectRows(Source, each [Date] >= Date.AddMonths(DateTime.LocalNow(), -12))), and consider using deployment pipelines for synchronization. Learn more here: Create and use Power BI template files (.pbit).
I hope this helps.If so,consider accepting it as solution.
Regards,
Pallavi.