Forum Discussion
Directquery: Large dimension tables 1 million row limit issue
HI, jjlek
1 million row limitation in direct query is the number of rows returned.
So for your case, please add some filter in the report to filter needed data.
Best Regards,
Lin
Community SupportHI, jjlek
1 million row limitation in direct query is the number of rows returned.
So for your case, please add some filter in the report to filter needed data.
Best Regards,
Lin
Hi v-lili6-msft ,
Thanks for your reply. Maybe I haven't made it explicitely clear in my initial post, but I'm not asking Power BI to return a million rows at all. I only want to return the few thousand rows, containing this year's sales numbers, with their relevant values from the dimension table. In other words, the report is already filtered.
However, my dimension table in total contains over 1 million rows. Power BI seems to run a query selecting ALL values from the dimension table and their relevant join keys, before making the join to the fact table. Thereby causing a million rows limit error because of the way that the query is split up.
The interesting thing is that when I hit "Refresh all" in Power BI desktop, the issue disappears. Only to reappear at a later stage. So maybe it's cache related?
Any further help would be appreciated.
- v-lili6-msft
hi, jjlek
What do you use to filter the report? You may try to add a filter in Report level filter.
Best Regards,
Lin
This issue still has not been resolved. It does however seem like the query behaviour from Power Bi is very inconsistent. When working with a star schema in direct query mode it sometimes selects the entire dim table before joining, but other times it does not do this. Meaning that in some cases having a dimension table larger than 1 million rows results in an error and in other cases it does not. This seems to be completely random however.
How could this behaviour be avoided?
