Forum Discussion
DAX running total optimization : Using date column from dimension vs date from fact
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.
tvaishnav 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 I will try to explain this. I can attach a sample PBI but it won't replicate the scale of the problem.
My understanding of best practice is as follows. Slicers / Filters should always come from dimensions and numbers that we use for computation should come from fact tables. Going by that logic, I always try to pull date column from date dimension when used in slicer or used as a column in visual. So I think VAR MaxDate = MAX(Date_Dim[Date]) is fine.
When I used date column from date dimension and use the measure you gave me, it just runs forever. However, when I use date column from fact table, it returns results pretty instantly but with repeated values (as in the case in my previous reply). I don't really want to use date from fact but I was just testing how things out.
Does that explanation help?
tvaishnav 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] < 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)