Forum Discussion
Optimize complex dax measure
Thanks for the suggestion AlexisOlson .
This works fine when i try it on the sample dataset which i have shared with you.
But the problem is all with when we have that whole table data and it takes almost 3min of time to render the visual.
This is where the users are all complaining about report to have better performance.
Please help. lbendlin AlexisOlson parry2k
Mohan128256 In my experience, there is no magic bullet that's going to let you materialize millions of rows in fractions of a second in a report, no matter how optimized your DAX is. As others have said in this thread, there is simply way too many rows and columns in your table visual to render in a fast way. To test this theory, I'd be curious to see how long it takes your table visual to load with a simpler DAX measure, like a SUM over a column in your fact table. It may not be ~3 minutes, but I bet you it will still take a bit of time to load.
In my opinion, I think the real solution here is working with your end users and see what they're trying to do with all that raw data. Perhaps you can negotiate with them and see if a different report design like a drillthrough/tooltip/single-select slicer will give them what they need without taking forever to load. That being said, it is our responsibility to give the business what they need per their requirements. So if your users truly need this much raw data, then you should help them understand that the cost of this approach will be a slow query, and there's no avoiding that.
Sorry to be the bearer of bad news here. I'd be interested in getting other people's opinions on this subject if there's something I'm missing. As someone who works in Finance (and they love their Excel spreadsheets), if there's a way to load a large amount of data very quickly in a table/matrix, then please let me know!