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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Does Power BI import everything before aggregating with Direct Query?

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 !

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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
Not applicable

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 !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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