Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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. "Summar...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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?