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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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

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. 

Hi @Anonymous 

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

@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
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.