Resultset of a query to external data source has exceeded the maximum allowed size

[teaguejs]

Hi,

I have built a calculated table using the following expression:

Missing From CMDB =

SELECTCOLUMNS (

    CALCULATETABLE (

        IT_Current,

        EXCEPT ( VALUES ( it_current[ip_address_rapid7] ), VALUES ( CMDB_Active[IP_Address] ) )

    ),

    "IP_Address_Rapid7", IT_Current[IP_ADDRESS_RAPID7],

    "Host_Name", it_current[host_name_rapid7],

     "operating_system", it_current[os_description]

)

Unfortunately when I run this I receive the following error:
<ccon>The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.</ccon>

[TomMartens]

Hey @teaguejs ,

there is a limit of 1M rows for directQuery, you can expand this limit if you are using Premium Capacity (Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn)

Nevertheless, you might use SUMMARIZE (SUMMARIZE – DAX Guide) to "group" the columns you want, before you use SELECTCOLUMNS to rename the columns.

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,
Tom

[teaguejs]

What would the query look like if I wanted to just show distinct values of "it_current[ip_address_rapid7]" within my calculated table function?