Simplified the FILTER Logic:
The original query used multiple IN VALUES checks within the FILTER function. This can be inefficient.
The revised query keeps the same logic but aims to streamline it. However, further optimization could involve using EXCEPT to filter out selected values more efficiently.
Combined Calculations:
The original query had nested CALCULATE functions, which can be resource-intensive.
The revised query combines the calculations into a single CALCULATE function. This reduces the complexity and potentially improves performance.
Simplified Multiplication:
The original query had separate CALCULATE functions for different parts of the multiplication.
The revised query combines these into a single multiplication operation: [UNIT] * SUM('SBI Actuals Agg (x100)'[QTY_USED]) * (1 + qq).
Here's the revised query again for reference:
notselected_customers =
VAR qq = (Quantity[Quantity Value] / 100)
RETURN
CALCULATE(
[UNIT] * SUM('SBI Actuals Agg (x100)'[QTY_USED]) * (1 + qq),
FILTER(
ALL('SBI Actuals Agg (x100)'[ch], 'SBI Actuals Agg (x100)'[mc], 'SBI Actuals Agg (x100)'[brand], 'SBI Actuals Agg (x100)'[ch3]),
NOT (
'SBI Actuals Agg (x100)'[ch] IN VALUES('SBI Actuals Agg (x100)'[ch]) &&
'SBI Actuals Agg (x100)'[mc] IN VALUES('SBI Actuals Agg (x100)'[mc]) &&
'SBI Actuals Agg (x100)'[brand] IN VALUES('SBI Actuals Agg (x100)'[brand]) &&
'SBI Actuals Agg (x100)'[ch3] IN VALUES('SBI Actuals Agg (x100)'[ch3])
)
)
)
These changes should help improve the performance of your DAX query.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
