topic Re: DAX Optimization help request in DAX Commands and Tips

DAX Optimization help request

Anonymous — Fri, 12 Jan 2024 21:24:50 GMT

Hi Everyone,

I have a query that take timeout error, can you please help me to optimise the dax query?

Query is below that;

DEFINE
VAR CA =
SUMMARIZE (
'FACT_SALES',
'FACT_SALES'[CLIENT_ID],
"client_xx",
IF (
CALCULATE (
SUM ( 'FACT_SALES'[MONTANTNET] ),
USERELATIONSHIP ( 'DIM Representant'[REPRESENTANT_ID], 'FACT_SALES'[REPRESENTANT_ID] )
) > 0.00001,
1,
0
))
RETURN
SUMX ( CA, [client_xx] )

Looking forward to your answers

Regards,

Re: DAX Optimization help request

lbendlin — Fri, 12 Jan 2024 23:27:14 GMT

Read this article

All the secrets of SUMMARIZE - SQLBI

Re: DAX Optimization help request

Dangar332 — Sat, 13 Jan 2024 06:35:32 GMT

hi, Anonymous

DEFINE VAR CA = addcolumn( all('FACT_SALES'[CLIENT_ID]), "client_xx", IF ( CALCULATE ( SUM ( 'FACT_SALES'[MONTANTNET] ), USERELATIONSHIP ( 'DIM Representant'[REPRESENTANT_ID], 'FACT_SALES'[REPRESENTANT_ID] ) ) > 0.00001, 1, 0 )) RETURN SUMX ( CA, [client_xx] )

Re: DAX Optimization help request

123abc — Wed, 17 Jan 2024 08:23:31 GMT

It seems like you are trying to create a calculated column in DAX using the SUMX and SUMMARIZE functions. Without knowing the exact details of your data model and the specific optimization needs, I can provide some general suggestions to improve the performance of your DAX query.

Avoid using IF inside CALCULATE: You are using an IF statement inside CALCULATE, which might lead to performance issues. Try to rewrite your DAX using FILTER or other relevant functions to avoid the nested IF.

DEFINE
VAR CA =
SUMMARIZE (
'FACT_SALES',
'FACT_SALES'[CLIENT_ID],
"client_xx",
CALCULATE (
SUM ( 'FACT_SALES'[MONTANTNET] ),
USERELATIONSHIP ( 'DIM Representant'[REPRESENTANT_ID], 'FACT_SALES'[REPRESENTANT_ID] )
) > 0.00001
)
RETURN
SUMX ( CA, [client_xx] )

Avoid using SUMMARIZE inside SUMX: The use of SUMMARIZE inside SUMX might not be necessary. Instead, try to use the underlying table directly in SUMX.

DEFINE
VAR CA =
CALCULATETABLE (
VALUES('FACT_SALES'[CLIENT_ID]),
'FACT_SALES'[MONTANTNET] > 0.00001,
USERELATIONSHIP ( 'DIM Representant'[REPRESENTANT_ID], 'FACT_SALES'[REPRESENTANT_ID] )
)
RETURN
SUMX ( CA, [client_xx] )

Check relationships and indexes: Ensure that your relationships are correctly set up in the data model. Also, check if there are indexes on the relevant columns for better query performance.
Use simpler expressions if possible: If your measure is still slow, consider simplifying the logic or breaking down the calculation into smaller steps to identify the bottleneck.

Remember that DAX performance optimization often depends on the specific characteristics of your data and data model, so it's essential to test different approaches and monitor the performance impact.

Re: DAX Optimization help request

123abc — Wed, 17 Jan 2024 08:23:33 GMT

Avoid using IF inside CALCULATE: You are using an IF statement inside CALCULATE, which might lead to performance issues. Try to rewrite your DAX using FILTER or other relevant functions to avoid the nested IF.

Avoid using SUMMARIZE inside SUMX: The use of SUMMARIZE inside SUMX might not be necessary. Instead, try to use the underlying table directly in SUMX.

Check relationships and indexes: Ensure that your relationships are correctly set up in the data model. Also, check if there are indexes on the relevant columns for better query performance.
Use simpler expressions if possible: If your measure is still slow, consider simplifying the logic or breaking down the calculation into smaller steps to identify the bottleneck.