Dax calculated column optimization

[yios]

Hi all,

I have created a calculated column with the below dax code
Previous Val =
var _date = MyTable[Date]
var _label = MyTable[label]
var _IssueDateTime = MyTable[IssueDateTime]
return
MAXX(
FILTER(MyTable,
MyTable[label] = _label && MyTable[Date] = _date && MyTable[IssueDateTime] < _IssueDateTime),MyTable[ShipDateTime])
)

My question:  Does it matter the sequence of columns with the speed of the query?

For example above query returns in 40 seconds, but if i put first MyTable[Date] = _date and then MyTable[label] = _label it return in 1 minute and 10 seconds.

Also if i omit MyTable[Date] = _date it never returns!! Error : There is not enough memory

Is there any recommendation for the sequence of columns( for example filter first the columns that will reduce your final dataset the most ...) 

[v-yingjl]

Hi @yios ,

Using EARLIER() function like @ amitchandak mentioned is acutally a way to optimize the calculated column in this case. Basically we would suggest you to create measure instead of calculated column to save memory because if the amount of calculation is large, it will take up more memory, resulting in insufficient memory and produce an error like your mentioned. 

You can refer this blog about measure and calculated column:

Calculated-columns-and-measures-in-dax 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[yios]

I tried your proposal to use measure instead of calculated columns but it takes a lot time the visual to be displayed due to complex calculations which must be run on the fly of a user reaction.

[yios]

Is there any documentation that says that earlier function is faster than using variables ??

I am asking because it runs in exactly the same time, no better performance.

[amitchandak]

@yios , Try like this if it works better

MAXX(
FILTER(MyTable,
MyTable[label] = earlier(MyTable[label]) && MyTable[Date] = earlier(MyTable[Date]) && MyTable[IssueDateTime] < earlier(MyTable[IssueDateTime])),MyTable[ShipDateTime])