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.
