Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I could need some help wiht the DAX formular for a new calculated table column.
My goal is to get the last datetime for a specific filter within the same table. My query is working fine with a small amount of data but it quickly runs into performance issues (OOM & Time).
For a better understanding of my goal you will find example data, the expected output and my current query below:
Given Table:
TaskID | Start DateTime | End DateTime | ||
... | ... | ... | ||
222 | 19.02.2020 06:54:47 | 19.02.2020 06:57:09 | ||
222 | 19.02.2020 06:57:09 | 19.02.2020 07:01:12 | ||
333 | 19.02.2020 06:58:52 | 19.02.2020 07:02:00 | ||
222 | 19.02.2020 07:05:15 | 19.02.2020 07:10:46 | ||
444 | 19.02.2020 07:12:41 | 19.02.2020 08:11:57 | ||
222 | 19.02.2020 07:13:33 | 19.02.2020 07:17:14 | ||
444 | 19.02.2020 08:11:59 | 19.02.2020 08:16:42 | ||
... | ... | ... |
Expected Output for the new column "Previous EndTime":
TaskID | Start DateTime | End DateTime | Previous End DateTime | |||
... | ... | ... | ... | |||
222 | 19.02.2020 06:54:47 | 19.02.2020 06:57:09 | 19.02.2020 06:53:23 | |||
222 | 19.02.2020 06:57:09 | 19.02.2020 07:01:12 | 19.02.2020 06:57:09 | |||
333 | 19.02.2020 06:58:52 | 19.02.2020 11:23:48 | 18.02.2020 17:52:45 | |||
222 | 19.02.2020 07:05:15 | 19.02.2020 07:10:46 | 19.02.2020 07:01:12 | |||
444 | 19.02.2020 07:12:41 | 19.02.2020 08:11:57 | 19.02.2020 06:32:11 | |||
222 | 19.02.2020 07:13:33 | 19.02.2020 07:17:14 | 19.02.2020 07:10:46 | |||
444 | 19.02.2020 08:11:59 | 19.02.2020 08:16:42 | 19.02.2020 08:11:57 | |||
... | ... | ... | ... |
Currently in use DAX:
Previous End DateTime = CALCULATE(
MAX('Table'[End DateTime]),
FILTER(
'Table',
'Table'[TaskID] = EARLIER(TaskID)
&& 'Table'[End DateTime] <= EARLIER('Table'[Start DateTime])
)
)
Additional Information:
The "Previous DateTime" has to be from the same TaskID. The "Start DateTime" does not have to be equivilant to the "Previous End DateTime". The gap between "Start DateTime" and "Previous End DateTime" can be 0sec but it could also be a gap of more than a month.
For the column "Previous EndTime" i can't use a measure because it shouldn't be affected by any additional filters and I have to do further calculations (e.g. difference between "Previous End DateTime" and "Start DateTime").
I'm pretty new to PowerBI and DAX, so I would be happy and thankful for any information, help, advices or a new perspectiv to solve this problem.
Hi,
Perhaps with a variable, it performs better?
https://www.sqlbi.com/articles/variables-in-dax/ , see bottom of article for a simular example.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Unfortunately I already tried that with the same issues that I'm running OOM and that it calculates for ages.
You can try to build it in Power M. I just posted an answer in the Power Query area that does exactly this.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |