Forum Discussion
Cumul sum performance improvements
as long as you have the some sort of time filter (Year, Month), [Current Cost] will actually run on smaller dataset, cause it will be evaluated only in that filter context (e.g. 2018 only)
the other measure:
1) determines the max date in the given filter context (coming from slicers, visuals etc.)
2) overwrites that filter context with ALL and applies the filter from 1) so everything before or equal to 2018. As FILTER is iterator it does the comparison for each row of the Dim_Date[PK_Date]
have a read here
https://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/
you could try this, my hope would be that it would save time by not evalulating MAX ( 'Dim_'[PK_Date] ) in each iteration, but I'm not sure how effective this would be
Cumul cost =
VAR _MaxDate =
MAX ( 'Dim_'[PK_Date] )
RETURN
CALCULATE (
[Current cost];
FILTER ( ALL ( 'Dim_Date'[PK_Date] ); 'Dim_Date'[PK_Date] <= _MaxDate )
)Thanks for the explanation.
I have extensively tried the parameters suggestion, it unfortunately does not result in faster times. Not worse either though, exactly the same.
Iit seems that even though it evaluates each row, it does not compute each time the max(), hance no improvement with parameter.
this video is quite helpful in regards to optimization
https://www.sqlbi.com/tv/dax-optimization-examples/
a bit over 1h, but very informative with hands on examples
