Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 =
The measure 'Remaining Time' returns the follow graph:
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
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
86 | |
85 | |
68 | |
67 | |
63 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |