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

[arnomics]

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

[amitchandak]

@arnomics , Try a measure like

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

[arnomics]

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?