Most optimized running total measure

[smpa01]

@AlexisOlson 

I am trying to write a running total measure that will be consumed immediately once evaluated by javascript to generate the viz.

Hence, to minimize the cumulative burden [DAX(to generate value to pass on to js) +js (in viz generation)] I want the most optimized running total measure.

I tried with a sample data and 3 different versions as follows

------V1------
RT =
VAR _period =
    MAX ( DateTbl[Period Number] )
RETURN
    CALCULATE ( [Budget], DateTbl[Period Number] <= _period )
-------V2--------
RT = CALCULATE([Budget], DATESYTD(DateTbl[Date]))

----V3-----
RT = 
CALCULATE(
	[Budget],
	FILTER(
		ALLSELECTED('DateTbl'[Date]),
		ISONORAFTER('DateTbl'[Date], MAX('DateTbl'[Date]), DESC)
	)
)

The comp is here for a 1.5mil Budget Table and they are almost the same

Is there anything else you can suggest that would drastically improve the timing (ideally lesser <10ms)

Thank you in advance.

[AlexisOlson · Accepted solution]

V1 is what I would use.

Since you're already under 50ms, I don't think there's much room for further optimization for the measure itself. If you're having performance issues, it's probably better to look at how you are calling the measure.

For example, if you have 200 data points that you're calling the measure for with a 50ms response time for each, that's a total of 10 seconds. However, you can do much better if you query all of the data points at once rather than a separate query for each one. SUMMARIZECOLUMNS is your friend here.

All that said, the DAX pattern for a cumulative sum is O(n^2) complexity (since DAX doesn't do recursion). A recursive running total computation can be O(n) and hence much faster for large sets. Consider computing the running total in JS if you can't get the DAX performant enough.

[smpa01]

Thanks @AlexisOlson , will report back here and let you know.

[smpa01]

@AlexisOlson  javascript even being super awesome and blazing fast, it lacks DAX's dynamic data modelling capacity (at least I don't know) and subsequently the filter context that actually makes Power BI reporting so awesome and dynamic.

Hence, to build viz through d3, I still need to rely on DAX to produce the data (multidimensional, filter context)  (through each measure super optimized) before passing on the whole DAX data structure ( SUMMARIZECOLUMNS getting generated through user interaction)d3 to create the rest of the user experience.

It is not d3 yet, but I will soon adopt this to a d3 custom viz, expense reporting 

Thanks once again.

[Daryl-Lynch-Bzy]

@smpa01- should you create an inactive relationship between Date and Budget and then include USERELATIONSHIP in the DAX calculation.  This should simplify the query that are executed.

[Daryl-Lynch-Bzy]

Hi @smpa01 - it is not clear why the Date is not directly connected to the Budget table in the Data Model or from the description above?  The umbigious join results in an additonal query and to a small delay.  This might help reduce the amount of time required by the formula engine. 

[smpa01]

Hey @Daryl-Lynch-Bzy 

not sure I understand your confusion. Date is a separate table as it is the norm in DAX/multi-dimensional model to use a separate Date table so that it can slice/filter from many many many other tables harmoniously and return the number on the viz.

If you are asking if I have Dates in budget-> yes, I do have Dates in Budget.

Can I use the Dates from Budget as a slicer? No, cause Budget[date] can't reach other tables. So I will only use DatesTable[Date] to sing the song cause it can reach all the places Budget[Date] can't reach.