Hi All,
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.
Hi @Pete673, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
An image of your data model is helpful for advice. Switch statements used for Financial reporting is inherently slow to be honest especially given the length of your template.
I would check your measures to ensure there are efficiently calculating.
I got to Marcos info yesterday and now understand the problem. So my current take is as follows:
My model is of a similar structure to Sam’ video on financial reporting. ie
a. model with fact table of general ledger numbers, dimension tables for depts, sites, dates and accunt categorisation.
b. only 1 to many relatiosnhips in a cascading fashion. No bidirectional at all and only 2 small tables necessary to form a “slight” snowflake model. Detached report template.
Whilst I have a large report template table when refreshing the measures for each page individually, the refresh is lightining fast - 1-2secs. Bearing in mind that 1000 of the 2000 report template rows are being calculated by a single measure this is just awesome.
I am writing separate measures for each “page” in the report template (call them page measures), then pulling then together in a single measure (call it Main measure) via a Switch statement dependant on Selected page from a slicer.
As I add each Page Measure to the Main Measure, the refresh page increases by around 2secs. So conclusion is that the dax is being worked through on each switch item even though the calculation is unnecessary. An If statement behaves in the same way.
As per Marcos comments on the subject I need a way of shortcircuiting the Switch statement. 3 ideas -
Put a Divide statement in where the Page measure is divided by an if statement - If(selected page = “pageName”, 1 , 0, Blank()) - Didn’t work.
Put the Page measure into a calculate function with filter context which will return no rows for the unselected pages. This makes sense but I’m not sure what to write to filter down the report template.
Use calculate table somehow to create a virtual table of the selected rows, then any non selected pages may return nothing quickly since the rows are not there to evaluate. Again I have no idea whether this approach would work nor any idea how to write it. Never used CalculateTable.
So i guess I need some help here and probably the second option is the one I need help with.
If you picture Sam’s PL report template; add a column in for “Page Description”, mark everythng to Gross margin % as Gross and everything underneath as Expense, this will create a scenario to work with.
Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum
Hi All
Seems this one was very difficult to resolve. To summarise the problem, the more options put into a Switch statement, the slower the DAX calculation, due to evaluation of all non required items in the Switch statement.
I have still not resolved this one and I guess we need microsoft to do something to make it really whizzy.
This is a shame because Sam’s financial showcase is absolutely the way to go, but for us would involve too many Switch items.
My work around is to go back to the financial model suggested by Imke Feldman, the BIAccountant. The number of switch lines here is fewer, but it does require you to import/power query rework a lot more data than Sam’s would and is less intuitive for less IT savvy accountants.
Nontheless, life goes on and we do have a working solution.
Would just be good to make it refresh faster.