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:

1. Use a star schema
2. Reduce column cardinality
3. Get rid of unnecessary fields
4. 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.