Forum Discussion
DAX Optimization Tips for Large Models
Hi Anonymous ,
Optimization of models and DAX measures is not that easy you need to check several parts of your code and understand where it's getting slow.
What I can tell you from the image I see is that having a many to many relationships (even with a single filter) it's not a good option to get a good performance, in this case I'm refering to the PBI_CHALLENGES and PBI_ELIGMEMBER relationship, but I can see that you also have other relationships like this.
Optimized models with millions and millions of rows need to be in a star schema and this is not the case in this example that is also part of your issues.
Instead of doing a measure to do the Challenge completions you can try and use a Field Parameter that will allow to run each of the metrics individually instead of them running on top of a switch.
Again this is just an high level perspective of what you present, further details need to be done analizing your model closely.
Hey Thanks for your replay..
I have tried and didn't work.. Any suggestions on better versin of the measure i have used
My first suggestion would be to clean up your Measures by deploying VARs. The reason for this are two-fold:
1. Makes code easier to read
2. Avoids re-calculating over and over values that only need to be calculated once in the code.
Unfortunately, while DAX is a low code language, it still presents pitfalls that require having a working knowledge of computer programming techniques.
HI Anonymous ,
Without knowing the model and the way it works it's difficult to pintpoint the error, we cannot refer please change this or that and the performance will be enhanced since there is a lot into play when refering to DAX and the Vertipaq engine.
