Decrease amount of data received from Direct Query

[Anonymous]

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!

[v-kelly-msft]

Hi @Anonymous ，

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.

Best Regards,
Kelly

[Anonymous]

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.

[apentyala]

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

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

Regards,

Avinash

[Anonymous]

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]

Please refer if this can help

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

[Anonymous]

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]

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]

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]

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.

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?