cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pessaTAP Frequent Visitor
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
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
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.
pessaTAP Frequent Visitor
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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors