DATEADDslows measure down

[Anonymous]

Hi everyone,

I have a measure that really slows down by using the DATEADD function.

CALCULATE(DISTINCTCOUNT(Employee[EmployeeID]) , DATEADD('Date'[Date],-1,DAY))

Is there a better way to get the same output?

Luuk

[Anonymous]

Hi Luuk,

Been in your shoes! Almost exact problem, except that in my case it was DATEADD -1 YEAR over a DISTINCTCOUNT, for Month-over-month calculations. (same thing for -1 month)

What I found tracing with DAX was that adding DATEADD multiplied the number of SE queries performed. It did not have a huge impact if the model was hot (thanks to DAX caching), but it had a huge impact (3x time) in cold models / clear cache / fist load.

What I did was change the DATEADD, which I understand is done by the FE, into something I hoped closer to the SE. This is what improved quite a a lot the measure in my case:

 /* --original query, relatively slow
VAR __clients_last_year = CALCULATE([clients],DATEADD(DateTable[Date],-1,YEAR))

RETURN
IF(ISBLANK([clients]),blank(),DIVIDE([clients]-__clients_last_year , __clients_last_year ))
*/

-- optimized -- note that this only works for single selections of date!

VAR __M_ANT = EDATE(selectedvalue(FactTable[date]) , -12)   -- EDATE adds months
VAR __clients_last_year= CALCULATE([clients], ALL(DateTable), FactTable[date] = __M_ANT)
RETURN
DIVIDE([clients] - __clients_last_year, __clients_last_year)

This only works for single selections of date, but was about 3x more performant in my use case in cold calculations of the measure (which was used very often in my reports).

In your case, to add one day, it is much simpler than using EDATE.. just add +1 to move one day forward.

Hope this helps!

[Anonymous]

If this slows down, then it might mean your model is incorrectly built. I've never seen a good model where you'd have a fast measure which would slow down after applying the transformation in question.

But without knowing the guts of your model, nobody will be able to tell you anything.

Best
Darek

[Anonymous]

Hi Darek,

It is difficult for me to determine where the model is incorrect.

I don't have problems with other measures.

The model consists a fact table and several dimensions (9).

The fact table contains employee data such as salary, absence, etcetera.

There is a row in the dataset for every employee every day, total about 20 million rows.

Is it possible other perspectives slow this perspective down?

Luuk

[Anonymous]

By the way.... Is your storage model Import or DirectQuery?

Best
Darek

[Anonymous]

Well, you know, whether a model is good or not depends on what you want to do inside it. One problem can have many substantially different formulations (just like in mathematics) depending on which angle you want to look at it...

However, if you have one fact table that's connected to dimensions in a *-to-1 fashion and date tables marked as Date Tables in the model (and if they're correctly designed), then I can't see a reason why the measure should be substantially slower than the others.

Have you compared the performance of the simple measure DISTINCTCOUNT(Employee[EmployeeID]) with the one in question?

Best
Darek

[Anonymous]

What could be the reason the measure slows down by the *- to -1 relations and the date marked table?

It is an import storage model and the measure DISTINCTCOUNT(Employee[EmployeeID]) performs without problems.

Luuk

[Anonymous]

No idea... but you should capture the query plans using DAX Studio. Then and only then will you be able to see the differences between the simple measure and the one that makes trouble and figure out where the problem lies.

Best
Darek