Forum Discussion
DAX running total optimization : Using date column from dimension vs date from fact
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 con...
tvaishnav You could try this:
To Date U/M =
VAR MaxDate = MAX('Date'[Date]) //Maximum visibile date
VAR __Table = FILTER(ALL('Productivity'), [Date] < MaxDate)
VAR ActualUnitsToDate = SUMX(__Table, [Units]) // Sum of units
VAR ActualHoursToDate = SUMX(__Table, [ActualHours]) //Sum of hours
RETURN DIVIDE(ActualUnitsToDate,ActualHoursToDate,0)
Greg_Deckler 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:
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.
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.