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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Troubleshooting slow cross-filtering

I am having some trouble with cross-filtering being extremely slow (20 seconds) compared to using a slicer to achieve the same result (<1 second).

 

My report consists of the following:

1. "Summary" - table visual. This contains StockCode, Description, and various stats around qty on hand, sales, etc. Data is DirectQuery (as I am integrating it with a PowerApp)

2. "Sales History" - matrix visual. This contains the last 12 month sales history by month and warehouse. Data is a different table, in Import mode, which links to the summary table on StockCode

 

Report usage: we analyse the summary lines one by one, selecting one to see detailed history in the other visuals.

For example, I will select StockCode 12345, and the Sales History matrix will update to show the last 12 months of sales for each warehouse on that specific StockCode.

 

The issue is that this is extremely slow - the Sales History visual takes more than 20 seconds to update after I click on a row in the Summary table.

When I use a slicer on StockCode to select the same line, the Sales History visual updates almost instantly (<1 second)

 

I have tried switching the Sales History data source to DirectQuery, but that did not make any difference.

 

When I make a copy of the Summary visual and include only 2 of the original columns (StockCode and one of the stats), cross filtering from this visual works well and quickly.

 

Why is it that cross-filtering from a table visual with more columns takes so much longer, given that both produce the same result set, and the data sources are linked by a single column (StockCode)?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I've worked around the issue using the following method:

 

1. "Cache" the results of the direct query into a table on my database.

2. Change the query for the summary table to query the newly created table.

 

It is now working extremely fast, due to the speed of doing a simple select query against a database.

 

This adds the complexity of having to keep that table updated for the report users, but at least the performance is better!

 

Would still be interesting to learn how to optimise cross-filtering queries, struggling to find any good reference material online.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

I've worked around the issue using the following method:

 

1. "Cache" the results of the direct query into a table on my database.

2. Change the query for the summary table to query the newly created table.

 

It is now working extremely fast, due to the speed of doing a simple select query against a database.

 

This adds the complexity of having to keep that table updated for the report users, but at least the performance is better!

 

Would still be interesting to learn how to optimise cross-filtering queries, struggling to find any good reference material online.

amitchandak
Super User
Super User

@Anonymous , if there are any bi-directional join, make them single directional.  Check the query that is generating and check if there are  indexes to support that query.

 

Also refer: https://www.youtube.com/watch?v=4kVw0eaz5Ws

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

@amitchandak 

 

Thank you for the reply.

 

The joins are single-directional.

 

How would an index assist in this issue? I am having an issue with the Import table - not the DirectQuery table.

To clarify:

1. The Summary table visual has the DirectQuery data source. This is the table visual where I select rows in order to cross-filter the other visual.

2. The Sales History matrix visual is an Import data source, so from my understanding indexes would not be relevant as the entire data set already exists in the model?

@Anonymous  , are taking data from both sources (import/direct) together. Can you share relationship diagram with Direct/import table labelled

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

@amitchandak 

After investigating using the performance analyzer I think I understand why the DirectQuery performance is important: it seems that the cross filtering does an entire query against the DirectQuery table, using an additional WHERE clause for each of the columns in the table visual.

 

Is there no way to tell the cross filtering operation to only use the StockCode column for cross filtering? To me it doesn't make any sense why it would query the rest of the columns, the only relevant column in this case is the StockCode column.

Anonymous
Not applicable

Yes, I am using a mix of Import and Direct.

As stated in the OP, I did also try using only DirectQuery for both tables, but the result was the same.

 

Data model:

Model.png

vw_PBI_Redundant... is the DirectQuery table I am using for the "Summary" table visual.

Sales History NotTraceable is the import table I am using for the "Sales History" table visual.

 

The other two tables in the model provide information on additional visuals in the report. Could they be impacting performance?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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