Measure Performance DateTime

[rasknud33]

Hi all

I am having performance issues for a Measure and I hope you can help.

I have made a model that tracks the progress of a number of IT-projects.

A project can start with an estimated duration of 100 hours. When a developer works 5 hours - the remaining time will be 95 hours. The next day a developer works 10 hours and the remaining time will be 85 hours. I am tracking this progress over time.

The data looks like the following (example data from a table called History_remaining):

Key	History Field	History New Value End	History New Value Start	History Old Value	History New Value
A-123	Remaining Time		10-03-2023 15:19	71	0
A-123	Remaining Time	10-03-2023 15:19	08-03-2023 17:58	72	71
A-123	Remaining Time	08-03-2023 17:58	02-03-2023 13:58	72,5	72
A-123	Remaining Time	02-03-2023 13:58	02-03-2023 11:03	72,75	72,5
A-123	Remaining Time	02-03-2023 11:03	01-03-2023 17:05	74,25	72,75
A-123	Remaining Time	01-03-2023 17:05	22-02-2023 10:14	74,75	74,25
A-123	Remaining Time	22-02-2023 10:14	17-02-2023 13:09	76,75	74,75
A-123	Remaining Time	17-02-2023 13:09	14-02-2023 16:02	77,25	76,75
A-123	Remaining Time	14-02-2023 16:02	13-02-2023 09:39	88,25	77,25
A-123	Remaining Time	13-02-2023 09:39	30-01-2023 15:10	98,25	88,25
A-123	Remaining Time	30-01-2023 15:10	20-12-2022 10:48	98,75	98,25
A-123	Remaining Time	20-12-2022 10:48	07-12-2022 12:47	99,5	98,75
A-123	Remaining Time	07-12-2022 12:47	05-12-2022 11:03	100	99,5
A-123	Remaining Time	05-12-2022 11:03	28-11-2022 14:51		100

I have made a measure which works as intended:

Remaining Time =

VAR measuredateDate =

MAX ( 'Dates'[Date] )

VAR RemainingHours =

CALCULATE (

SUM ( History_remaining[History New Value] ),

History_remaining[History New Value Start] <= measuredateDate,

History_remaining[History New Value End] >= measuredateDate

|| History_remaining[History New Value End] == BLANK (),

REMOVEFILTERS ( 'Dates' )

)

RETURN

RemainingHours

The measure 'Remaining Time' returns the follow graph:
Example 1

The problem I have is with performance - when all the data is in the model it takes about 35 seconds to run the measure.

I have troubleshootet and I have learned that the problem is with the [History New Value Start] and [History New Value End] columns since they are DateTime columns. If I convert DateTime to Date - the measure only takes about 3 seconds to run. However, the graph then does not compile the data in the correct way. See example below:

Key	History Field	History New Value End	History New Value Start	History Old Value	History New Value
A-123	Remaining Time		10-03-2023	71	0
A-123	Remaining Time	10-03-2023	08-03-2023	72	71
A-123	Remaining Time	08-03-2023	02-03-2023	72,5	72
A-123	Remaining Time	02-03-2023	02-03-2023	72,75	72,5
A-123	Remaining Time	02-03-2023	01-03-2023	74,25	72,75
A-123	Remaining Time	01-03-2023	22-02-2023	74,75	74,25
A-123	Remaining Time	22-02-2023	17-02-2023	76,75	74,75
A-123	Remaining Time	17-02-2023	14-02-2023	77,25	76,75
A-123	Remaining Time	14-02-2023	13-02-2023	88,25	77,25
A-123	Remaining Time	13-02-2023	30-01-2023	98,25	88,25
A-123	Remaining Time	30-01-2023	20-12-2022	98,75	98,25
A-123	Remaining Time	20-12-2022	07-12-2022	99,5	98,75
A-123	Remaining Time	07-12-2022	05-12-2022	100	99,5
A-123	Remaining Time	05-12-2022	28-11-2022		100

I suspect that this is because the measure uses the entire DateTime data to see where in the graph it should put the different numbers. When it only has the date this is getting messed up. Is there a way to get the result from example 1 but using a DAX code similar to example 2 so that the performance is improved?

I would be thankful if anyone has a suggestion to fix this and help me to improve the performance of my measure.

Best regards,

Rasmus

[lbendlin]

Use the Performance Analyzer to grab the query for the visual, then fire up DAX Studio to examine the query plan.  Look out for the number of records column. If you see numbers in the gazillion range there you got yourself a case of the cartesian productivitis.  SQLBI.com have tons of videos on measure performance optimization.