General Solution for Problem: external data source has exceeded the maximum allowed size of 1m rows

[BryonScruggs]

I have several reports which need to be converted from import to direct query mode. Unfortunately, one of my dimension tables exceeds 1 million rows so many operations which were formerly possible in import mode are no longer supported in direct query mode. For example, I receive this error when a complex matrix vizualization tries to render, or when I try to access a slicer tied to a dimension table larger than 1m rows. 

Here is a simplified data model. The table "attr_Matter_vw" has 1.6 million rows. 

Does anyone know a general solution to this problem? I can't easily filter down attr_Matter_vw because many calculations require access to the entire data set. 

thanks, 

Bryon

[BryonScruggs]

Hi AlexBr, 

I believe you are correct - the best solution to this problem is to beg my technology team to allow import mode reports! 

thanks, 

Bryon

[Anonymous · Accepted solution]

When you say 'need', is this an absolute constraint? If no, suggest the dimension tables should not be direct query. If the constraint is absolute, suggest you first look to optimise the dimension tables at the source, next you can attempt query optimisation within PBI and failing that, if you are on Premium Capacity, you can amend settings to extend row limit. Some links below to get you started.

Roche’s Maxim of Data Transformation – BI Polar (ssbipolar.com)

Use composite models in Power BI Desktop - Power BI | Microsoft Learn
https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-premium-workloads#max-intermedia...

DirectQuery optimization scenarios with the Optimize ribbon in Power BI Desktop - Power BI | Microso...