Skip to main content
cancel
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

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"))

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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