Does Power BI import everything before aggregating with Direct Query?

[Anonymous]

Hi Everyone,

I'm on Power BI desktop using Direct Query to connect to a database on Amazon Redshift.

I'm getting an issue when I try to get the count of a column from one table grouped by the values of another table. 

When I write the query on redshift it returns ~70 values with the count of the first column next to it. When I try to do this in Power BI however, I get an error saying 

Error Message:
The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

My question is, how does aggregation work with Power BI? If the query it's sending Redshift already has a group by in it, I shouldn't be running into this error. Is Power BI really importing all of the values from both tables before attempting to group them?

If so is there a way to work around this?

Thank you,

Nikhil

[Anonymous]

@Anonymous - Is there a Relationship defined between the 2 tables? 

[Anonymous]

@Anonymous  Yes there is, I just checked to make sure.

[Anonymous]

With DirectQuery, the DAX needs to be translated into the data source's query language (SQL). So you could run a trace on the source DB to find out what SQL is being run. 

What is the DAX of the Measure used to count rows? And what is the SQL that it is converted into?

[Anonymous · Accepted solution]

It looks like the issue was with the complexity of the Query Editor operations? I removed one of the steps which was "remove column" that someone added and it works now...

I did find this in the documentation to support it:

"If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor"

Not too sure at this point why the remove column step was problematic but I can work with this. Hopefully this'll help others if they run into this issue.

I appreciate your help @Anonymous !