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.
I have a data base that contains a number of assets and status changes, here is the data.
assetUUID | Status | timeHappenedUTC |
d150 | Take Action | 6/3/2021 1:36 |
d151 | Take Action | 6/3/2021 1:36 |
d152 | Take Action | 6/3/2021 1:38 |
d150 | Take Action | 6/3/2021 1:43 |
d151 | SOS | 6/30/2021 22:50 |
d151 | SOS Cancel | 6/30/2021 22:50 |
d150 | SOS | 8/26/2021 3:18 |
d150 | Take Action | 8/26/2021 3:24 |
d150 | SOS Cancel | 8/26/2021 3:28 |
my first target was to create a duration formula between "SOS" and "SOS Cancel", this I have done.
My second request was to track the duration between "SOS" and "Take Action" status but there are more "Take Action" Status than the "SOS" Status.
Is it possible to calculate a duration between "SOS" and "Take Action", only if it falls between "SOS" and "SOS Cancel" ?
I have no clue where to even begin to build something like this,
I was also thinking there are other status changes that are different than "Take Action", is it possible to build something that would calculate a duration by using "Take Action" status then any status that happens before the "Take Action"?
I'm just begenning to get my head around DAX and starting to get some good results but this has me completly stumped.
O.K. thank you for the forumula it makes allot of sense, now I've tried it however my results are showing up with all no results the durations are coming up with zero as the results, I tried to change the duration to seconds from minutes and there are no duration numbers associated to it. I'll keep working on it.
Hi @pgiorgi1 ,
You can try the measures below for 2 cases you've described:
1. calculate a duration between "SOS" and "Take Action", only if it falls between "SOS" and "SOS Cancel"
2. calculate a duration by using "Take Action" status then any (other) status that happens before the "Take Action"
duration#1 =
VAR c_asset = SELECTEDVALUE ( T[assetUUID] )
VAR c_status = SELECTEDVALUE ( T[Status] )
VAR c_time = SELECTEDVALUE ( T[timeHappenedUTC] )
VAR prev_time =
CALCULATE (
MAX ( T[timeHappenedUTC] ),
FILTER (
ALLSELECTED ( T ),
T[timeHappenedUTC] < c_time && T[assetUUID] = c_asset
)
)
VAR prev_status =
CALCULATE (
MAX ( T[Status] ),
TOPN (
1,
FILTER (
ALLSELECTED ( T ),
T[timeHappenedUTC] < c_time && T[assetUUID] = c_asset
),
T[timeHappenedUTC]
)
)
VAR next_status =
CALCULATE (
MAX ( T[Status] ),
TOPN (
1,
FILTER (
ALLSELECTED ( T ),
T[timeHappenedUTC] > c_time && T[assetUUID] = c_asset
),
T[timeHappenedUTC]
)
)
RETURN
IF (
c_status = "Take action" && prev_status = "SOS" && next_status = "SOS Cancel",
DATEDIFF ( prev_time, c_time, MINUTE ),
0
)
duration#2 =
VAR c_asset = SELECTEDVALUE ( T[assetUUID] )
VAR c_status = SELECTEDVALUE ( T[Status] )
VAR c_time = SELECTEDVALUE ( T[timeHappenedUTC] )
VAR prev_time =
CALCULATE (
MAX ( T[timeHappenedUTC] ),
FILTER (
ALLSELECTED ( T ),
T[timeHappenedUTC] < c_time && T[assetUUID] = c_asset
)
)
VAR prev_status =
CALCULATE (
MAX ( T[Status] ),
TOPN (
1,
FILTER (
ALLSELECTED ( T ),
T[timeHappenedUTC] < c_time && T[assetUUID] = c_asset
),
T[timeHappenedUTC]
)
)
RETURN
IF (
c_status = "Take action" && prev_status <> "Take action",
DATEDIFF ( prev_time, c_time, MINUTE ),
0
)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |