I'm using a SQL database, containing over hundreds of million rows. To get data the database without too long loading times, I'm using slicers to limit the size of it. however, even with the slicers, Power BI tries to collect every data, filtered by tthe selections. What I want to ask, can I set somewhere the maximum amount of data collected from a Direct Query to something like a 1000? Because the visuals immidiately reload when users reselect a slicer, which causes a reload to occur every 6 seconds.
Thanks in advance!
Hi @Zlus ，
Try to use data filter:
Go to "edit queries">click on the "drop- down" button of data>"data filters">"Custom filter":
Just filter the data you need.
Hello @v-kelly-msft ,
if I do this, I am forced to switch to Import mode, which I want to prevent because the data needs to be as fresh as possible.
Also, I need all the data contained within the database; by filtering them in Power Bi Desktop I can minimize the amount of data. If I were to filter them before loading, I wouldn't have access to every data.
Filtering in Desktop works, however Power Bi Desktop always sends queries ignoring the allowed amount to be returned. Often, the visuals end up showing the error that more than a million rows have been loaded (Because the are sometimes cases where the user hasn't selected anything and canceling the big query doesn't work by reselecting anything). In my case, I just want to see about a 1000 rows. If there's somewhere a option to change the value such as: "Restrict the amount of data received by queries in Direct Query to: ", this will be every amazing.
thanks for that! I believe my queries became a little faster.
Nonetheless, my problem still doesn't really subside: the time needed is too long because the amount of the data/count of rows loaded for the visuals doesn't change.
sadly, I'm only using table visual to show the collected data. This means I can't aggregate them and can't use a composite model.
It would be nice if I could set a limit, how much data the table should load.
You can use the parameter to get the updated data for a particular period.
please have a look on below
Hello @apentyala ,
I checked your suggestions.
Because I'm not using Query Editor to transform the Query tables, the first link doesn't help me.
The second page is very interesting; due to that, I have just learned that tables only show 500 rows before loading again. However, it doesn't work in my case, since a simple refresh of reselecting one slicer causes the table to load for at least 5 min (it's also way longer when nothing is selected, even though I've created a measure to avoid showing any rows in the table when nothing is selected).
If anyone could tell me how to prevent a visual from loading/ refreshing when nothing is selected, I would appreciate it greatly.
Check out the News & Announcements to learn more.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.