I am struggling with Performance and I now think pretty much its down to the DAX.
The model looks ok, mostly joining on surrogate keys and have stripped out what I dont need.
Some of the design I am stuck to. So we have a period slicer for YTD , closed YTD, closed period , open period etc and about 10 other date ranges
Another design I am stuck with is I also have two sales values (different currency) in the fact table for reporting.
I am struggling to get some Matrix visualisations to complete within 10 seconds. When you open a report then ther is one Matrix and some other visualisatuions.
The Matrix contains an object such as Sales territory and values are
current value , prior value, growth and growth percent.
Current Value is a large switch based on the period slicer.
“Closed Year”, CALCULATE([Sales],‘Dim Calendar’[Closed Year] = 1),
“Closed Year -1”, CALCULATE([Sales],‘Dim Calendar’[Closed Year-1] = 1)
I then do similar to get values for prior periods
Any gains here that should help me out with all of the calculations.
Is there a good guide on how to improve the performance? Step by Step?