Forum Discussion
Running total poor performance with large date table
ITManuel This is the alternate running total method: Better Running Total - Microsoft Fabric Community
You also might be interested in this post where it is a similar situation with a large date dimension that wreaks utter havoc on CALCULATE. CALCULATE Challenge - Round 1 - Microsoft Fabric Community
Hi Greg_Deckler & lbendlin ,
thank you, I'm surprised and impressed about this content in relation "CALCUHATE" 🤣😂🤣.
I was not aware that there is an alternative to CALCULATE for running totals and so many other things.
I tested CALCULATE vs. SUMX for running totals against different sizes of Date and Fact table. SUMX is faster with a very large Date table. However it becomes slower than the solution with CALCULATE with smaller sized Data tables depending on the size of the fact table.
I tested the following:
RT CALC =
CALCULATE (
SUM ( TasksBLTP[TaskBaselineCost] ),
ALL ( 'Date' ),
'Date'[Date] <= MAX ( TasksBLTP[TimeByDay] )
)
RT SUMX =
VAR _MaxDate =
MAX ( TasksBLTP[TimeByDay] )
VAR _T1 =
FILTER ( ALLSELECTED ( TasksBLTP ), TasksBLTP[TimeByDay] <= _MaxDate )
VAR _Result =
SUMX ( _T1, TasksBLTP[TaskBaselineCost] )
RETURN
_Result
Date table with 41 Years: SUMX is faster 1,8x to 46x faster depending on rows in Fact table
Date table with 11 Years: CALCULATE or SUMX is faster depending on rows in Fact table
Date table with 4 Years: CALCULATE or SUMX is faster depending on rows in Fact table
The pbix file can be downloaded under https://we.tl/t-3GUEmfy0fE
Since even SUMX takes 10+sec. for most selections in my real model, I'm thinking of implementing a separate Date table only for the TaskBLTP which only contains the number of rows required for the current selection.
Any other ideas?
Best regards
Keep measuring and then use whatever is appropriate in your scenario. Follow the best practices around how to filter, when to filter etc. The key aim is cardinality reduction without impacting the final result. Cartesian turtles all the way down.
