Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

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

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors