Direct Query taking unreasonably long

[scoobyloong]

Hi everyone, this is my first post so i'm not too sure if i'm in the right page. 

My dashboard currently uses direct query to filter through 2 scenarios (out of a list of a 1000+) and obtain rather granular data that spans into the thousands of rows. It seems that applying a hard filter in power query to limit the query on power query to a list of 2 scenarios, there is a substantial speed up of about 10x. 

Basically i'm trying to calcaulte percentiles of data that is halfhourly as a measure, but it seems that DAX/Powerquery iterates through all scenarios on a halfhourly level to check if it belongs in that scenario, explaining why the query is so slow. 

[v-juanli-msft]

Hi @scoobyloong 

DirectQuery is a direct connection to data source. Data will NOT be stored in Power BI model.

Power BI will be a visualization layer, then query the data from data source every time.

In the DirectQuery case, when users visit a Power BI report, Power BI sends queries in real time to the underlying data source.

Once the data source returns with the query data, then the report is rendered.

As a result, your report performance in these cases depends largely on the performance of the underlying data source.

Learn more about performance:

Power BI performance best practices

You could Use Performance Analyzer to examine report element performance

With DAX Studio, you could analyze DAX queries for performance issues, and  improve the writing of DAX queries.

https://www.sqlbi.com/articles/capturing-power-bi-queries-using-dax-studio/

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.