DirectQuery row limit issue

[PepeSilvia]

Hi all, Having an issue I hope someone can help with - I have a report in which I am trying to use a composite model / mixed storage mode (whichever term is best).
I have a core model with member data which is being imported via direct query, and other tables which are being imported into my model. I am linking my main member table in the core model (300k rows) to the main table in my imported model (17k rows). These tables are linked by a key (member ID).
I can create measures which use data from both models - say to aggregate members by a dimension in the smaller imported table (say membership type), however if I try to add another element from the larger table (say to split by gender as well as membership type) then after some time I get the error message saying the query is trying to return more than 1M rows from the DirectQuery source.

Anyone have an idea how I am returning more than 1M rows in this scenario? I assume it might be how my dax statement is being interpreted, however I'm at a loss..!

Please let me know if I am missing any useful info here, thanks!

[v-yalanwu-msft]

Hi, @PepeSilvia ;
Try to reduce DAX usage to a minimum and super-optimize your SQL database so that it will answer quickly to the specific queries Power BI will generate.

Solved: Direct Query 400Million Row of Data - Performance ... - Microsoft Power BI Community

Power BI Fast and Furious with Aggregations - RADACAD

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @PepeSilvia ;

This is by design, You can take a look at the premium capacity.it's a limitation with DirectQuery mode, refer to the official document: Use DirectQuery in Power BI Desktop;

• Limit of 1 million rows returned on any query: There's a fixed limit of 1 million rows placed on the number of rows that can be returned in any single query to the underlying source. This limit generally has no practical implications, and visuals themselves aren't going to display that many points. However, the limit can occur in cases where Power BI isn't fully optimizing the queries sent, and there's some intermediate result being requested that exceeds the limit. It can also occur while building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity would hit this limit if there were more than 1 million customers, until some filter were applied.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PepeSilvia]

Thanks for getting back to me. 

I am aware of the 1M row limit, however what I dont understand is with these table sizes how I am hitting 1M rows being returned from the source at all? Eager to understand if there is some efficiency or optimisation issues at the heart of my issue.