DAX running total optimization : Using date column from dimension vs date from fact

Question

Problem : I have a fact table called Productivity. It tracks actual units of work performed and actual hours it took to perform the work. It also includes forecast hours and forecast units. It is connected to three dimensions as shown below. Note that those dimensions are shared between multiple fact tables which are not relevant to this question. The productivity fact table tracks data on daily baisis i.e. data is tracked for each date. I am unable to roll it up because of the business need. I need to calculate divide&nbsp; running total for units by running total for hours.&nbsp;&nbsp;Total number of rows in fact table&nbsp;: 32931185&nbsp;I have slicer on each dimension.&nbsp; I have added filters to slicer so that if one slicer is selected, it narrows down values in other slicers.&nbsp;&nbsp;Here is the measure I am trying to compute:&nbsp;&nbsp;&nbsp;To Date U/M =

VAR MaxDate = MAX('Date'[Date]) //Maximum visibile date

VAR ActualUnitsToDate = CALCULATE(
                                SUM(Productivity[Units]) // Sum of units 
                                ,ALL('Date'[Date])       // Ignore filters over date
                                ,'Date'[Date]&lt;=MaxDate  // All filter on the date
                                )

VAR ActualHoursToDate = CALCULATE(SUM(Productivity[ActualHours]) //Sum of hours
                                   ,ALL('Date'[Date]) // Ignore filters over date
                                   ,'Date'[Date]&lt;=MaxDate) // All filter on the date

RETURN DIVIDE(ActualUnitsToDate,ActualHoursToDate,0)&nbsp;&nbsp;&nbsp;This measure runs forever. So to scale down the problem, I tried calculating only ActualUnitsToDate.When I use date column from dimension table, ActualUnitsToDate takes forever. When I use date column from productivity fact table, it takes about 25000 ms. Still not really acceptable but atleast it returns the result.&nbsp;&nbsp;Questions :&nbsp;- What does date column from fact perform better than the one from date dimension?- What couldd cause the simple running total calculation so slow. I understand that this is intensive considering the size of the dataset ( 32931185 rows). What can I do to improve the performance?&nbsp;EDIT&nbsp; : It seems that performance is also impaced by which column I use in visual. Date from fact vs date from dimension.

greg_deckler · Answer

tvaishnav&nbsp;You could try this:
To Date U/M = 
VAR MaxDate = MAX('Date'[Date]) //Maximum visibile date
VAR __Table = FILTER(ALL('Productivity'), [Date] &lt; MaxDate)
VAR ActualUnitsToDate = SUMX(__Table, [Units]) // Sum of units 
VAR ActualHoursToDate = SUMX(__Table, [ActualHours]) //Sum of hours
RETURN DIVIDE(ActualUnitsToDate,ActualHoursToDate,0)

greg_deckler · Answer

tvaishnav&nbsp;So, probably something I'm not understanding about your data model or where the values in your visual are coming from. If you get your max date in context and then FILTER ALL of your fact table where the date column in that fact table is less than that date, then I don't know why you would get the same value for all dates unless something else is wonky. If you have a 1:* relationship between your date table and fact table and it is single direction, then if you used the date column from the fact table in that visual, then the MAX of 'date'[date] would return the largest date in the date table and that would explain it. So, if that is the case, use MAX('Productivity'[Date]) instead in the measure.

greg_deckler · Answer

tvaishnav&nbsp;Right, so if you use date column from the fact table in the visual, then the measure should be:
To Date U/M = 
VAR MaxDate = MAX('Productivity'[Date]) //Maximum visibile date
VAR __Table = FILTER(ALL('Productivity'), [Date] &lt; MaxDate)
VAR ActualUnitsToDate = SUMX(__Table, [Units]) // Sum of units 
VAR ActualHoursToDate = SUMX(__Table, [ActualHours]) //Sum of hours
RETURN DIVIDE(ActualUnitsToDate,ActualHoursToDate,0)
Otherwise, because of the unidirectional relationship the other version would always return the maximum date in the date table with no filters (so whatever the biggest date is in your date table)

greg_deckler · Answer

tvaishnav&nbsp;Well, the best practice is whatever works in your particular situation. 😉&nbsp; Generally speaking through, what you described is the conventionally accepted wisdom. However, it's not foolproof as you saw when using the date from the productivity table was running faster.
&nbsp;
Running totals by day across 33M records is likely going to be problematic through however you try to slice it. Basically, for each date, you have to put all 33M records into memory, then filter that down but in some cases you are still dealing with tens of millions of rows over and over again for certain dates. You might try filtering your visual so that it only returns a set number of days. Also, you might try calculating this in Power Query or as a calculated column so that it runs as part of refresh instead of every time the visual is loaded.

tvaishnav · Answer

Greg_Deckler&nbsp;Your measure returns same value for each date. I can see why. VAR __Table gives a intermediate table. SUMX performs sum over the entire __Table without breaking it by date. Here is how it looks:&nbsp;I think I need to use calculate function around SUMX. But I am unable to change context on __Table via calculate. Any thoughts on how I can accomplish this?I do understand what you are trying to do here. It is certainly faster.&nbsp;&nbsp;However, this measure also performs pooly when my date column in the visual comes from date dimension. It returns results instantly if I use date column from fact table in the visual. Why would that be? It is a simple one to many unidirectional relationship.&nbsp;

Forum Discussion

DAX running total optimization : Using date column from dimension vs date from fact

8 Replies

Related content

Optimize Measures

DAX performance optimization

Fabric Cost optimization

Optimize Running Total

degenerate dimensions