Forum Discussion
DAX Performance Tuning - Income Statement (Third Attempt)
Hi JRogersLebara,
First of all, the standard matrix visualization is almost exactly 3 times faster than the custom visual:
I'm not familiar with this visual by profitbase, but if you can manage to produce the required report from the matrix instead you're cutting 2/3's of the loadtime.
Furthermore, i took a look into the calculations and it doesn't appear that any individual calculation is weighing things down. I would propose changing the background-structure to avoid the SWITCH/FILTER combination, as i would not expect this level of performance for 100K rows.
I'm usually running something along the lines of this:
Which is then unpivoted in the transform so that both summarizations (gray) and rows can be mapped to a GL Account dimension table.
Hope this helps!
/ J
Hi JRogersLebara,
First of all, the standard matrix visualization is almost exactly 3 times faster than the custom visual:
I'm not familiar with this visual by profitbase, but if you can manage to produce the required report from the matrix instead you're cutting 2/3's of the loadtime.
Furthermore, i took a look into the calculations and it doesn't appear that any individual calculation is weighing things down. I would propose changing the background-structure to avoid the SWITCH/FILTER combination, as i would not expect this level of performance for 100K rows.
I'm usually running something along the lines of this:
Which is then unpivoted in the transform so that both summarizations (gray) and rows can be mapped to a GL Account dimension table.
Hope this helps!
/ J
Hi tex628,
The Profitbase matrix offers formatting benefits and also is very fast to use once slicers have been set (the initial increased load time when opening the tab or changing a slicer is offset by instantaneous drill-down. The normal matrix visual has to load every time the user drills down a level in the matrix, which we decided was more frustrating than the extended load after a slicer change).
In terms of your second suggestion, I think I partially follow but not completely - I have created a second file sort of following your advice but am not clear on how to replicate an identical matrix to the first file I shared using your solution - maybe you wouldn't mind taking a look and pointing out where I've misunderstood you?:
It may also be worth noting that our GL accounts don't follow a perfectly linear mapping (i.e. revenue may be 100-150, then CoS 151-153, then revenue again 154-160 etc)
Thanks,
Jake