DAX Performance Optimization for High-Granularity Analysis

Hi akim_no ,

Thank you for the time you invested in testing the proposed approaches and for providing detailed feedback. Based on our review of your model design, validation results, and the performance behavior you observed, we can confirm that your current DAX implementation is functionally correct, and the results align with the expected business logic (Price + Volume + Mix = Total Variance).
The performance behavior observed with Formula Engine limitations inherent to high-cardinality, measure-driven calculations, rather than an issue that can be resolved through further DAX syntax optimization.

Given this, the viable optimization paths are architectural rather than syntactic. We recommend you to try reviewing any of the following options best aligns with your functional and architectural constraints:

1. Pre-aggregate data
Introduce a physical aggregation at the lowest grain permitted by the business rules (for example, Item × Period × Currency). This approach significantly reduces the volume of data processed during PVM calculations while maintaining result accuracy.

2. Separate FX conversion from PVM logic
Evaluate dynamic exchange rates once at the Period/Currency level and reuse the converted values within the PVM calculations. This avoids repeated FX evaluations inside high-cardinality iterators and reduces Formula Engine workload.

3. Prevent unfiltered evaluation
Restrict the execution of PVM measures to scoped contexts such as when a customer, product group, or date range is selected. This prevents the engine from evaluating tens of thousands of items simultaneously in an unfiltered matrix scenario.

Hopefully this helps,
Thank you.