Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Query to external data source has exceeded the maximum allowed size of '1000000' error - Directquery

Hey all,


I have an issue which I've been unable to get my head around for quite some time & I was hoping someone out here could help me troubleshoot.


I have a Composite Model where I've got a mix of DirectQuery & Import tables. My Fact Table (Contract) is DirectQuery, one of the table that joins to Contract is called (Service) & is an Import, one other table that joins to Contract is called (Mobile) & it is a Directquery. 


My aim is to isolate Mobile IDs from (Mobile) table that are flagged as 'Prepaid' in the (Service) table. I've created the below Measure which gives me an accurate count as expected.


The problem occurs when I try to bring in a Table visual with Mobile ID & then apply a Visual level filter as Flag = 1. The 'Flag' IDs or "Prepaid" count of IDs is under 4000 rows. However, the issue is that my Mobile table has over 2 Million rows. When I apply the visual level filter, I get an error message of "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."


I'm guessing my Measure is not optimized enough & that there is a better way of writing a Measure or isolating the 4000 odd Mobile IDs to use on my Report.


FWIW - the Measure I have works fine, if I add another visual level Filter on Mobile ID where, I'm looking for IDs starting with "03". Any help would be greatly appreciated! Thanks.



            VALUES ('Mobile'[ID]),
            'Service'[Type] = "Prepaid"
        "PrepaidFlag", 1



Super User
Super User

@arnomics , Try a measure like


calculate(DistinctCount('Mobile'[ID]), filter('Service','Service'[Type] = "Prepaid"))

Thanks @amitchandak . I tried this Measure and yes, it gives a perfect/correct count for 'Prepaid' Services. However, when I try to use this as a Filter on a Table Visual where I'm trying to show Mobile IDs that are part of the Measure, I get the same error ""The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows." 


I'm not sure exactly what the problem is but I think it is still querying the whole Mobile table. Would a Measure that returns Y or N where we have a 1 help?

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors