Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

9 REPLIES 9
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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
Super User
Anonymous
Not applicable

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,

Anonymous
Not applicable

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.

Anonymous
Not applicable

Did you ever find a satisfactory solution for this? I'm in the same boat; using DirectQuery and I don't want to bother my poor database with heavy queries until the data has been filtered down enough.

 

The only solution I've been considering is to reduce the maximum row limit for DirectQuery on a capacity level. If you go to the Power BI service -> open the Admin Portal -> click on your capacity's name and then scroll down, you'll find the "Max Intermediate Row Count" setting where you can set it to your desired number.

MrDoctor_0-1678483406327.png

 

That will reduce the amount of data for everything connected to that capacity though. I would like to do something similar, but just for a single visual. Any ideas out there?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.