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

How to make my report slicer filter more quickly?

Hi,

 

I am very new to Power BI, so apologies if this is just some fundamental knowledge I don't have yet.

 

I am connecting to external data in Google BigQuery to a view (that takes about 10 secs to run, FYI) of all our historical sales data by product, by DirectQuery. This produces data of about 350,000 rows. I then have a matrix to display the data in detail, with multiple slicers to filter the data.

 

My problem is that whenever I amend the slicers to filter for something the matrix takes ages to refresh and display the data, about 20 secs every time.

 

What are my options here to make this display quicker? Do I need a lot less data, so maybe filtering the view query down to only this year (although I'm surprised if this is the solution, as thought PowerBI was able to deal with crazy amounts of data..)? Any other options, as ideally want to keep historical data in...?

 

I did try it out with Import, instead of DirectQuery, but that does not give the desired outcome of allowing the end user to get real-time data whenever they view the report. It does drastically reduce the time to filter the data, though. Also to note, when I filter the slicers it does not run the query again and update the data, only does that on the refresh button.

 

Thanks,

 

Benji

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have since found out that the answer to this questions: "Am I right in thinking that with a filter on the slicer, it doesn't rerun/refresh the underlying query?" is no. Every slicer filtering causes a rerun/refresh of the underlying query, so that is why it's taking so long to filter/slice.

 

My solution had to be a work around, and using two different reports. An import report of the same data that has scheduled refreshes, that can be used to get not quite fully real-time data, but functions quickly. And then the Direct Query version that you can use to get as up to date data as possible, but takes a long time to interact with.

 

I don't think there is any other way of getting the same data with the same output. I have optimised everything else as much as possible already as per the links above.

View solution in original post

6 REPLIES 6
v-xicai
Community Support
Community Support

Hi  @Anonymous ,

 

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

Anonymous
Not applicable

I have since found out that the answer to this questions: "Am I right in thinking that with a filter on the slicer, it doesn't rerun/refresh the underlying query?" is no. Every slicer filtering causes a rerun/refresh of the underlying query, so that is why it's taking so long to filter/slice.

 

My solution had to be a work around, and using two different reports. An import report of the same data that has scheduled refreshes, that can be used to get not quite fully real-time data, but functions quickly. And then the Direct Query version that you can use to get as up to date data as possible, but takes a long time to interact with.

 

I don't think there is any other way of getting the same data with the same output. I have optimised everything else as much as possible already as per the links above.

Hi  @Anonymous ,

 

Glad to hear the issue is resolved,  you can accept the right reply as solution, that way, other community members would benefit from the solution.

 

Best regards

Amy Cai

v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

There some tips to optimize the data source performance in DirectQuery mode, see: https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#guidance-for-using-directquery-successfully.

 

You can also refer to these links:

https://docs.microsoft.com/en-us/power-bi/refresh-data#best-practices ,

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance ,

https://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques .

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks Amy, I'll have a read of them, but from quick scan looks like I'm following those already.

 

Am I right in thinking that with a filter on the slicer, it doesn't rerun/refresh the underlying query? And that's only done when clicking the refresh button? It almost feels that on every filter it refreshes, but that would not be what I want at all, and would definitely be reason for taking so long.

 

Thanks,

 

Benji

Hi @Anonymous ,

 

For your first question, by default, slicers only allow a single selection to be made. Allowing multi selection in filters can cause some performance issues, because as the user selects a set of items in the slicer (for example, the ten products they are interested in), then each new selection will result in queries being sent to the backend source. Whilst the user can select the next item prior to the query completing, this does result in extra load on the underlying source.

 

For your second question, the time then taken to refresh the visual will depend on the performance of the underlying data source.

 

If data is changing, there is no guarantee of consistency between visuals: Different visuals, whether on the same page or on different pages, might be refreshed at different times. Thus if the data in the underlying source is changing, there is no guarantee that each visual will be showing the data at the exact same point of time. Indeed, given that sometimes more than one query is required for a single visual (for example, to obtain the details and the totals) then consistency even within a single visual is not guaranteed. To guarantee this would require the overhead of refreshing all visuals whenever any visual refreshed, in tandem with the use of costly features like Snapshot Isolation in the underlying data source.

This issue can be mitigated to a large extent by again selecting Refresh, to refresh all of the visuals on the page. And it should be noted that even if using Import mode, there is a similar problem of guaranteeing consistency if importing data from more than one table.

 

Please read the document about the DirectQuery carefully :https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#other-implications .

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.