If I were to tackle something like this, here’s the general approach I would take:
- Before even delving into the data model and the report, I would develop an analysis plan listing the questions I need the report (or far more likely, reports) to be able to address
- I would then try to arrange these questions into like groups with the idea of trying to bin them into separate reports, each supported by a much simpler data model geared specifically to addressing that subset of questions/analyses
- in terms of data modeling, I would try to eliminate the bidirectional and 1:1 relationships, and get each data model conforming to a star schema
- In the process I would arrange each data model in the waterfall/Collie structure with the dimension tables cascading down into the fact tables (in the current data model, it’s almost impossible for me to figure out even what is a fact table and what is a dimension table)
- I would also work to eliminate every unused table, field, calculated column and measure (there are numerous tools that can help you do this - have a look at this thread)
Only after I’d done all of this to dramatically simplify this report and underlying model would I begin delvving into the DAX, visuals, etc.
I hope this is helpful. Good luck!
- Brian