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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
dmkblesser
Helper I
Helper I

Any best practice for using direct query connection?

The company has decided to use direct query to connect to the cloud data source for its tracebility. We are aware of the 1 million rows limitation and the potential slowness when querying data from the source. Additionally, the table view tab will no longer be available in Power BI and you lose the ability to easily look up tables and data.

 

Can anyone share their experience regarding the impact of solely using direct query on reports, from report building to accessing reports?

 

For report developers, will it be more difficult to write measures due to the limitations? Are there any limitations in the query editor when it comes to transforming the data?

 

How do you handle large data when you encounter the limitation issue? I am aware that the admin can change the settings, but if we leave the settings unchanged, what is the best approach to deal with large data?

 

Any suggestions or shared experiences would be greatly appreciated!

4 REPLIES 4
dmkblesser
Helper I
Helper I

Thanks @aj1973 for your response. I want to understand the 1 million rows limiation a bit more. 

 

I have a table with more than 70 million rows. The table has  a column named 'price' and another column named 'date'.

 

When I wrote a measure basically just sum(price) and I'm able to put it in a card visual and display it without any issues. In this case, the underlying data is already exceeding 1 million rows, why does this measure work? 

 

On another attempt. I put the `date` and `price` in a table and select `do not summarize` for the `price`, so in this case, my data again is exceeding 1 million rows, but the table show up okay. 

 

Now, when I add the measure to the table, I'm getting the limitation error message. Just would like to understand why it happens at this time instead of the previous two attempts. 

 

Thank you

Here's what is happening:

However, the limit can occur in cases where Power BI doesn't fully optimize the queries sent, and requests some intermediate result that exceeds the limit.

 

The limit can also occur while building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity could hit this limit if there are more than 1 million customers, until you apply some filter. The error that returns is: The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

 

I got it from here

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hi @aj1973, I have read the documents in the past. In the example you quoted, is TotalSalesQuantity a measure? 

 

In my previous response,  "On another attempt. I put the `date` and `price` in a table and select `do not summarize` for the `price`, so in this case, my data again is exceeding 1 million rows, but the table show up okay.

Isn't this result contradictory to the example from the documentation? 

aj1973
Community Champion
Community Champion

@dmkblesser 

A limit of 1 million rows can return on any query. There's a fixed limit of 1 million rows that can return in any single query to the underlying source. This limit generally has no practical implications, and visuals won't display that many points. However, the limit can occur in cases where Power BI doesn't fully optimize the queries sent, and requests some intermediate result that exceeds the limit.

 

Meaning if you have optimized queries then the implications are null

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors