Slow DAX with a variable

katieandemil — Mon, 29 Apr 2019 09:51:03 GMT

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.

topic Slow DAX with a variable in DAX Commands and Tips

Slow DAX with a variable