topic Assistance Required with Query Performance Issue in DAX Commands and Tips

Assistance Required with Query Performance Issue

Pavan_123456789 — Wed, 02 Oct 2024 15:57:29 GMT

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(

[Corporate Cust Current Year Rank] <= 'Top N Range'[Top N Range Value],

[Current Year Qty],

IF(

HASONEVALUE('Unique Customers'[CORPORATE_CUSTOMER]),

IF(

VALUES('Unique Customers'[CORPORATE_CUSTOMER]) = "Others",

CALCULATE(

[Current Year Qty],

FILTER(

ALL('Unique Customers'[CORPORATE_CUSTOMER]),

[Corporate Cust Current Year Rank] > 'Top N Range'[Top N Range Value]))))

Re: Assistance Required with Query Performance Issue

lbendlin — Wed, 02 Oct 2024 16:21:27 GMT

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? Is the dataflow accessed in import mode?

Re: Assistance Required with Query Performance Issue

Pavan_123456789 — Wed, 02 Oct 2024 16:59:57 GMT

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

Re: Assistance Required with Query Performance Issue

lbendlin — Wed, 02 Oct 2024 17:04:10 GMT

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.

Use DAX Studio to examine the query plan and make adjustments.

Re: Assistance Required with Query Performance Issue

Pavan_123456789 — Thu, 03 Oct 2024 05:52:16 GMT

Hi lbendlin ,

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.

Re: Assistance Required with Query Performance Issue

Anonymous — Thu, 03 Oct 2024 07:20:23 GMT

Hi Pavan_123456789 ,

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.

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

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Assistance Required with Query Performance Issue

Pavan_123456789 — Thu, 03 Oct 2024 07:53:24 GMT

HI Anonymous

Thank you for your response.

I wanted to clarify that I currently have the option to use only the Pro license.

If you have any suggestions or additional information regarding this, I would greatly appreciate it.

Re: Assistance Required with Query Performance Issue

lbendlin — Thu, 03 Oct 2024 11:50:45 GMT

These results are weird. Can you please show the data model in each case?