cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jashshah
Helper I
Helper I

Error: Resultset of a query to external data source exceeded the maximum allowed size of 100000 rows

Hello,

I am trying to connect Power BI with one of the Oracle Exadata's table (which has more than 1M records) and I am facing "Result set of a query to external data source exceeded the maximum allowed size of 100000 rows" error.

There is one slicer and couple of tables in the dashboard. I have already tried the following,

1. Enabled the query reduction from "Options" by checking Slicer and Filter selection
2. Tried to implement Top-N filter functionality for the attribute used in the Slicer

The strange thing is that the dashboard worked fine when the data source was IBM Netezza and now when I changed the source to Oracle Exadata I started seeing this issue. Everything is the same in terms of number of tables, slicer and even the data is same. Not sure where am I going wrong!

Please advise!

Thanks,
Jash

1 ACCEPTED SOLUTION

Hi @Jashshah ,

 

You can try to create rank column like DAX below.

 Rank= RANKX(FILTER(Table1 ,Table1 [Order# ]=EARLIER(Table1 [Order# ])),Table1 [Sales],, Desc, Dense)

Then create a filter measure like DAX below, then put measure Filter1 in the Visual Level Filter of goal visual, and setting the Filter1 as "is not blank".

 

Filter1= IF(MAX(Table1[Rank])<=1000000, 1, BLANK())

1.png

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi, I'm also facing same issue.

Could please help me to solve this issue.

v-xicai
Community Support
Community Support

Hi  @Jashshah ,

 

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

v-xicai
Community Support
Community Support

Hi @Jashshah ,

 

You can use SQL query to limit the data rows, Like code below.

 

SELECT TOP ( 1000001 )

        [t1].[Order Number],

        [t1].[Order Line Number]

FROM   [t1]

 

By the way, Oracle Exadata connector is unavailable in Power BI currently. You can post your new idea here https://ideas.powerbi.com/forums/265200-power-bi-ideas , to improve the Power BI.

 

It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

 

While you can try to use the ODBC connecter to connect to Oracle Exadata, refer to this case to find the steps.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xicai

 

Thank you so much for your response. I am able to connect to Oracle Exadata successfully there is no with that. The issue is row limitation error of 1000000. 

I belive if I use the below mentioned SQL it will restrict my entire resultset, what I want is to restrict the number of values that the slicer returns. Meaning when someone tries to search for a particular oder# the slicer should restrict the results to top-N based on the Order# entred and not bring the entire population of Order# which is more than 1M. Is there a way wherein we can achieve this? 

 

Thanks, 
Jash. 

Hi @Jashshah ,

 

You can try to create rank column like DAX below.

 Rank= RANKX(FILTER(Table1 ,Table1 [Order# ]=EARLIER(Table1 [Order# ])),Table1 [Sales],, Desc, Dense)

Then create a filter measure like DAX below, then put measure Filter1 in the Visual Level Filter of goal visual, and setting the Filter1 as "is not blank".

 

Filter1= IF(MAX(Table1[Rank])<=1000000, 1, BLANK())

1.png

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi,

I'm also facing same issue.Can you please explain the formula once and also in direct query , the rankx function is not available. How to achieve that? 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.