Following on from my previous post I’ve spent some time reworking my model in line with your suggestions on the videos. Previously I had the Report template connected to the fact table and was bringing in aggregated amounts via SQL server query to make totals work. Your solution with working with a detached report format table is really helpful and will simplify future maintenance for less IT literate accounting staff.
However whilst initially the speed was there, as I expand the Dax required to get to the end result, the refresh speed is slowing terribly and I still can’t see a fix.
The fact table is around 1million rows,The report format table is 2000 rows long in order to provide the necessary information acoss 9 motor dealerships covering around 50 departments. Around half of those rows are dedicated to gross profit analysis for the various makes and models of vehicles we sell. The remainder breaks into 40 or so “pages” across 12 or so report categories to cover, Service, Parts, Bodyshop etc etc. My intention (and this bit works) is to have slicers for time, dealership, department, reportname with a single matrix showing the numbers. Then essentially only a single report will populate the matrix.
I have started a switch statement with a measure to manage the model gross profit, and then started to write measures separately for each page. I then have a final switch statement (Call it MANumber) which looks at SELECTED VALUE for the Report page and brings in the appropriate measure.
Initially speed was fantastic but as I add more to the switch statement the more it slows down. If I comment out everything on MANumber except one measure, again speed comes back.
**I guess somehow the code is not ignoring the dax on the non-required items in the switch statement.**Also tried an IF statement, just in case, but that was just as poor performance wise.
I’m puzzled as to what to do next? Separate report format tables for each page? - but then how do I create a slicer for report picking? And just to cap it all, the model will also be used for paginated reports or put on our SSAS server to aid pdf production monthly - and we know how tough SSRS is!! So single report template table is the the way I’d like to go but no idea how to maintain the speed as I add more “page measures”
I hope all that makes sense and look forward to your kind asistance once more. If you need my pbix, then happy to share but need a secure way of getting it to you so that it is not seen on the forum. As you can appreciate lots of confidential financial info in there.
Look forward to hearing from you