Slow DAX with a variable

[katieandemil]

Hi,

I've come across now several times and issue in performance when I use a variable, and it's odd because I had issue or large table, but recently on a new project I've used it on a small table and the same issue.

The DAX should simply display results for each month (1 year only) and filter the data using month max date.

Simple, but it takes over 40 seconds with a variable and when I try date (which only works for 1 month) is very fast.

I've also try to combine date function with max (code below), just too see if there is a difference. No difference, still slow.

Please see below DAX example

Test Measure =
var SlowOver40sec = max(DimDate[Date]) + 1
var SlowTooOver40sec = DATE(2019,month(max(DimDate[Date])+ 1 ),1)
var FastBelow1sec = DATE(2019,4,1)
RETURN
CALCULATE(SUMX('Historical Table',1)
,'Historical Table'[ChangeDateTime]< FastBelow1sec
,all(DimDate))

I would appreciate any explanation of why it's so slow with a variable.