cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pessaTAP
Frequent Visitor

Direct Query - Exceeded Limit Unexpectedly

I'm attempting to use Direct Query, while creating a Data Model with several tables, some of which have more than 1mil rows... here is a simplified example. Both DIM_CUSTOMER and tbl_F_INVOICES have > 1mil rows.

2019-06-26_11-39-04.png

 

 

So my issue begins when I try to build a visual drawing data from both of these tables.

On the Left is a table visual showing data ONLY from tbl_F_INVOICES, filtered to a single date so that it will not exceed any limits; 

On the Right is a table visual showing data ONLY from DIM_CUSTOMER, with a filter applied to select only the ID "1619" which corresponds to the data in the Left hand table.

(note: I disabled the filter interactions between Left and Right sides; i.e. Inv Date and Company only apply to Left, CustomerID only applied to Right.)2019-06-26_11-43-55.png

Each of these tables work fine on their own, the issue is when I try to join them... So I choose to add "Name" from DIM_CUSTOMER, and I get the "Exceeded 1 mil rows" error, despite the fact that before bringing in the Customer data I have applied a filter so that it "should" only fetch the handful of customer records the filter calls for (i.e. the customer names associated with the 9 Invoices that I've filtered down to);2019-06-26_12-01-40.png

 

 

and furthermore it is able to display ALL of the Invoice data and ALL of the Customer data without any filters applied on either side... making it very confusing to me why I cannot display them together with a filter applied to limit results.

 

Is there a way around this issue? My ultimate goal is to create a Data Model, in Direct Query mode, with all of the relevant tables for a group of reports; so that I may use that dataset as a starting point for many reports rather than start getting data and modeling from scratch each time. 

 

NOTE: these tables are in fact from different databases (on the same server); I have read that Direct Query will not work when the data is from different databases, however I have used it successfully on some tables from different databases which did not have such a high amount of records, and I would expect a different error message if this was the issue... but please inform me if I'm incorrect and this is in fact only due to the tables are from different databases.

1 ACCEPTED SOLUTION

Accepted Solutions
pessaTAP
Frequent Visitor

Re: Direct Query - Exceeded Limit Unexpectedly

I figured out a workaround - rather than selecting the individual tables themselves in "Get Data" (i.e. check the box next to each table), if I specify one of the tables' databases as my source, then write a SELECT query that JOINs the two tables, I am able to get the records from both tables in a single object without hitting the 1mil row error. 

 

I would still love to know if there's a better way to do this... it seems very strange that I can't do it in a straightforward way. 

 

If I can't find a better way I suppose I will create a View that does the table join on the SQL side, and load that up in the future instead of doing this join inside the Get Data GUI. 

View solution in original post

3 REPLIES 3
pessaTAP
Frequent Visitor

Re: Direct Query - Exceeded Limit Unexpectedly

I figured out a workaround - rather than selecting the individual tables themselves in "Get Data" (i.e. check the box next to each table), if I specify one of the tables' databases as my source, then write a SELECT query that JOINs the two tables, I am able to get the records from both tables in a single object without hitting the 1mil row error. 

 

I would still love to know if there's a better way to do this... it seems very strange that I can't do it in a straightforward way. 

 

If I can't find a better way I suppose I will create a View that does the table join on the SQL side, and load that up in the future instead of doing this join inside the Get Data GUI. 

View solution in original post

Microsoft v-cherch-msft
Microsoft

Re: Direct Query - Exceeded Limit Unexpectedly

Hi @pessaTAP 

I'm afraid there's no other better ways.You may write a SELECT query or do that on the SQL side.

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
pessaTAP
Frequent Visitor

Re: Direct Query - Exceeded Limit Unexpectedly

@v-cherch-msft  thank you!

Would you be able to give me a brief explanation of this behavior?

Meaning why each of these tables can be loaded into visuals separately, without filters limiting their results displayed - however when combined, we hit this issue.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors