Assistance Required with Query Performance Issue

Question

I am currently facing an issue where the DAX query runs successfully with SQL Server as the data source. However, when using a data flow as the source, the query exceeds the time limit.Could you please assist me in identifying why this discrepancy is occurring and recommend any potential solutions to improve performance in the data flow environment?Corporate Cust Current Year Sales =IF(&nbsp; &nbsp; [Corporate Cust Current Year Rank] &lt;= 'Top N Range'[Top N Range Value], &nbsp; &nbsp; [Current Year Qty],&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; HASONEVALUE('Unique Customers'[CORPORATE_CUSTOMER]), &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Unique Customers'[CORPORATE_CUSTOMER]) = "Others", &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current Year Qty], &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL('Unique Customers'[CORPORATE_CUSTOMER]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Corporate Cust Current Year Rank] &gt; 'Top N Range'[Top N Range Value]))))

lbendlin · Answer

where the DAX query runs successfully with SQL Server as the data source. However, when using a data flow as the source, the query exceeds the time limit.
Was that SQL Server data source accessed in Direct Query mode?&nbsp; Is the dataflow accessed in import mode?

pavan_123456789 · Answer

Hi lbendlin&nbsp;Thank you for your response.both the SQL Server and the data flow are accessed in Import mode, not DirectQuery.

lbendlin · Answer

In that case the data source shoud not matter as in both scenarios your query is running as a DAX query against the semantic model, and not against the original source in Direct Query mode.
&nbsp;
Use DAX Studio to examine the query plan and make adjustments.

pavan_123456789 · Answer

Hi lbendlin&nbsp;,I’ve encountered a performance issue when running the same DAX query across two different data sources. When I run the query against our SQL data source, it completes in about 393 ms. However, when running the same query against the Data Flow as the data source, it takes significantly longer—around 34 seconds (34,000 ms).I’m unsure why this discrepancy is occurring. Could you please help me understand what might be causing the slower performance when using the Data Flow source? Any guidance or suggestions on how to optimize the query Please Help me.&nbsp;

anonymous · Answer

Hi&nbsp;Pavan_123456789&nbsp;,
&nbsp;
In general, Dataflow handles query differently than SQL Server. Often the choice is to use DirectQuery with dataflow.
If your data source is a large dataset, you may choose to build aggregated tables into the model to improve performance and retain detail data where necessary. Please note that this only applies to Premium or Premium Per User as you need to be able to turn on Enhanced Compute Engine Settings for Dataflow.

&nbsp;
For more details, you can refer to below document:
Using DirectQuery with dataflows - Power BI | Microsoft Learn
Premium features of dataflows - Power BI | Microsoft Learn
User-defined aggregations - Power BI | Microsoft Learn
&nbsp;
Best Regards,Adamk Kong
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

Forum Discussion

Assistance Required with Query Performance Issue

7 Replies

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Assistance Required – Power BI Workspace Refresh Failing with AccessDenied Error

Assistance Required - Power BI Visual Matrix

Assistance Required on DAX Logic for QoQ FCST in Matrix Visual

Assistance Required for Power BI Dashboard Refresh Configuration in On-Premises Environment