Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have machine data here. Almost all missions contains several actions. But I want the total duration of a specific mission. How to create a calculated column where I get the total duration of a mission.
My sample table is like this. The last columns "TotalTime" is my wanted outcome. If its not possible to get the duration in HH:MM:SS then seconds will do the same. I can convert it later on:
MissionNumber | ActionName | Date | Starttime | Finishtime | TotalTime | |||
1 | Start of Action | 28.07.2021 | 28.07.2021 08:00:00 | 28.07.2021 08:01:00 | 00:10:00 | |||
1 | Moving | 28.07.2021 | 28.07.2021 08:01:00 | 28.07.2021 08:05:00 | 00:10:00 | |||
1 | Delivering | 28.07.2021 | 28.07.2021 08:05:00 | 28.07.2021 08:09:00 | 00:10:00 | |||
1 | Finish of Action | 28.07.2021 | 28.07.2021 08:09:00 | 28.07.2021 08:10:00 | 00:10:00 | |||
2 | Start of Action | 28.07.2021 | 28.07.2021 09:00:00 | 28.07.2021 09:02:00 | 00:18:00 | |||
2 | Moving | 28.07.2021 | 28.07.2021 09:02:00 | 28.07.2021 09:09:00 | 00:18:00 | |||
2 | Delivering | 28.07.2021 | 28.07.2021 09:09:00 | 28.07.2021 09:15:00 |
| 00:18:00 | ||
2 | Finish of Action | 28.07.2021 | 28.07.2021 09:15:00 | 28.07.2021 09:18:00 | 00:18:00
|
For example MissionNumber 1 has a total duration of 10 Minutes. Which is the difference between the first action of that mission and the finish time of the last action of that very mission.
Thank you very much in advance.
Best.
Solved! Go to Solution.
Duration =
VAR __t = FILTER( INFO, INFO[MissionNumber] = EARLIER( INFO[MissionNumber] ) )
RETURN
MAXX( __t, INFO[Finishtime] ) - MINX( __t, INFO[Starttime] )
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Duration =
VAR __t = FILTER( INFO, INFO[MissionNumber] = EARLIER( INFO[MissionNumber] ) )
RETURN
MAXX( __t, INFO[Finishtime] ) - MINX( __t, INFO[Starttime] )
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |