Troubleshooting slow cross-filtering

[iron_dinges]

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)?

[iron_dinges · Accepted solution]

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]

@iron_dinges , 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

[iron_dinges]

@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?

[amitchandak]

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

[iron_dinges]

@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.

[iron_dinges]

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:

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?