Performance with Direct Query, role filtered data

[Jlacenski]

I am working on a dashboard that will be using Direct Query to several large tables (100M rows). Row level filtering is being used to filter the results. From the query logs, we see that PowerBI is loading the full dataset each time, instead of pushing the filtering selection down to the SQL level. This is causing unacceptable wait times. Is there any way to have PowerBI force the filtering predicate down to the SQL query level?

[Eric_Zhang]

---

@Jlacenski wrote:

I am working on a dashboard that will be using Direct Query to several large tables (100M rows). Row level filtering is being used to filter the results. From the query logs, we see that PowerBI is loading the full dataset each time, instead of pushing the filtering selection down to the SQL level. This is causing unacceptable wait times. Is there any way to have PowerBI force the filtering predicate down to the SQL query level?

---

@Jlacenski

That's some limitation on the Direct Query, especially applying RLS on a huge dataset. Check Important considerations when using DirectQuery.

Can you break down the report to sub reports? 100M rows is quite huge.

[serveroverload]

Hi,

Adding to a similar note, is there a limitation to the number of rows, after which the processing according to you would become slower? I deal with roughly 50K rows in my reports where RLS is applied at the Power BI level and was wondering if the solution would scale considering the limited amount of data?