SumX Formula Help for Optimal Performance

First of all, you should post the data model.

Second of all, no wonder this takes forever... You should know that:

1. Using a measure inside an iterator (like FILTER) is very slow if the cardinality of the iterator is big. Hence, iteration with a measure over a fact table will almost always be agonizingly slow and you'll never live to see the end of the calculation.

2. Nesting iterations is also a very bad idea (depending on the cardinality of the iterated tables).

3. DAX will be fast and easy ONLY WHEN your model is correct and built following the best practices. If your model sucks, then you'll start writing DAX which nobody - even you - will understand, especially after a while. Do you want it?

4. If your model is right, then DAX will be SIMPLE. SIMPLE means: easy to understand and to make changes to.

First, please paste your full model in here. Second, if you can, please put the file on a shared drive (like OneDrive) and send us a link to it so that we can play with the code and the model.

Thanks.

Best

Darek