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

Decrease amount of data received from Direct Query

Hallo everyone,

 

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!

8 REPLIES 8
v-kelly-msft
Community Support
Community Support

Hi @Zlus

 

Try to use data filter:

Go to "edit queries">click on the "drop- down" button of data>"data filters">"Custom filter":

Annotation 2020-02-04 162958.png

 

Just filter the data you need.

 

Best Regards,
Kelly

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.

Try This - Maybe it will help you to limit the Data

 

https://www.youtube.com/watch?v=c-ZqToc85Yc

 

Regards,

Avinash

Hi @apentyala,

 

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.

amitchandak
Super User IV
Super User IV

Please refer if this can help

https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak,

 

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.

apentyala
Helper II
Helper II

You can use the parameter to get the updated data for a particular period.

 

please have a look on below

 

https://community.powerbi.com/t5/Desktop/How-to-restrict-data-in-query-editor/td-p/406415

 

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-data-points

 

Regards,

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors