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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
arnomics
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.

 

 

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

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@arnomics , Try a measure like

 

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.