1M row limitation with 67 rows

[Anonymous]

Hi!

I am new to Power BI and I am getting a weird error

I am working in Direct Query Mode, and built a matrix with columns as months with 3 values each column.

I also have a date slicer, if the slicer has more than 3 days, I get the following error: "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."

The thing is, the variable in rows, doesn't have more than 67 different values. I even filtered this variables to only show top 5, but this error keeps on showing.

Anybody has any idea why this is happening and how to solve it?

Thanks in advance 🙂

[v-kelly-msft]

Hi @Anonymous ,

There are limitations for directquery,for references,pls click here.

But in fact, it wont influence the result.

Best Regards,
Kelly

[Anonymous]

Hi Kelly,

The problem is that the result is not showing ...

Thaks for your time.

[dm-p · Accepted solution]

Hi @Anonymous,

It's difficult without getting details of your DirectQuery model or what it's querying against, but is all your matrix data coming from a single table/query in your document?

With DirectQuery, Power BI needs to pull in all applicable data it needs to know about first, and will aggregate it afterwards - once it's finished loading, or the row limit has been reached.

One of the advantages of using the import approach, is that Power BI has all the data already present in the model and can figure this out without having to query outside of itself, and you typically don't see these issues here. Of course, DirectQuery has its own particular advantages 🙂

What I suspect this means is that even though you have 67 unique values, they are repeated many times over for all the rows of the result that's pulled back via DirectQuery before Power BI can attempt to filter it down to the unique values.

To confirm, it's worth profiling the query that's actually being run against your data source (if possible) when you attempt to get the matrix data, and this will either confirm that the table of data being sent to Power BI is indeed capping at 1,000,000 rows.

If you aren't using them already, a way to mitigate this would be to see if you can work to get the repeated lower-cardinality attributes set up as a separate table that only returns the distinct combinations - this is commonly referred to as a dimension table and it might be worth reading up further on the concept of star schemas, to see if this can help mitigate your situation.

The following documents are a great place to get started:

• Power BI documentation
• SQLBI - The importance of star schemas
• RADACAD - Power BI Basics of Modeling: Star Schema and How to Build it

If the person modelling the initial data source hasn't taken this into consideration then it's a likely cause of the problems you're seeing and it would be worth investigating whether turning your model into a star schema is possible for the fields you are interested in. If you already have a star schema then these fields are clearly candidates for a dimension table of their own.

I hope that this helps you in your investigations. Good luck!

Daniel

---

If my post helps, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂

[Anonymous]

Hi Daniel,

Thank you for your answer.

I think that in this case, Power BI is just not the solution.

I have a lot of data to query from a ig database, and this error is just getting old and boring at this point..

I apreciate your time 🙂 You were helful