Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys,
Can you please help me, I would like to derive to this measure where in:
Minimum Time before Failure = (Latest Ticket Created Time/Date)-(Previous Resolve Time/Date)
Note: Must be orderered by Resolve Time/Date. And Result must be on a Duration format like the "Duration" column.
This is a bit tricky as I am working on this also. Here's the dax that I'm working with:
Solved! Go to Solution.
Should only calls with the same priority be used when deterimining the previous resovled date and time? If so we just need to read it for the current row the use it as a filter in the PreviousResolved VAR:
Miniumum Time Before Failure = VAR CurrentResolve = 'Table'[ResolveTime/Date] VAR CurrentCreated = 'Table'[Ticket Created Time/Date] VAR CurrentPriority = 'Table'[Call Priority] VAR PreviouseResolved = CALCULATE( MAX ( 'Table'[ResolveTime/Date] ), ALL ( 'Table' ), 'Table'[ResolveTime/Date] < CurrentResolve, 'Table'[Ticket Created Time/Date] < CurrentCreated, 'Table'[Call Priority] = CurrentPriority ) VAR NumDays = DATEDIFF( PreviouseResolved,'Table'[Ticket Created Time/Date], DAY ) VAR DaysReturn = SWITCH ( TRUE(), NumDays = 0, BLANK(), NumDays = 1, NumDays & " day ", NumDays > 1, NumDays & " days " ) RETURN DaysReturn & FORMAT ( 'Table'[Ticket Created Time/Date] - PreviouseResolved, "hh:mm:ss")
Hello @icdns
Want to make sure I am understaing. I put this measure into my sample as a calculated column.
Previous Resolve = VAR CurrentResolve = 'Table'[ResolveTime/Date] RETURN CALCULATE( MAX ( 'Table'[ResolveTime/Date] ), ALL ( 'Table' ), 'Table'[ResolveTime/Date] < CurrentResolve )
It pulls the highest Resolve Time/Date that is before my current rows Resolve Time/Date but it gets a little strange on ticket 2 becasue it was open the whole time that ticket 3 was opened and closed so the next lowest Resolve Time/Date is from Ticket 3. We can fix it with another filter to only pull the highest resolve time/date from earlier tickets but wanted to make sure that is what you want.
Previous Resolve 2 = VAR CurrentResolve = 'Table'[ResolveTime/Date] VAR CurrentCreated = 'Table'[Ticket Created Time/Date] RETURN CALCULATE( MAX ( 'Table'[ResolveTime/Date] ), ALL ( 'Table' ), 'Table'[ResolveTime/Date] < CurrentResolve, 'Table'[Ticket Created Time/Date] < CurrentCreated )
Hi jdbuchanan71,
Thank you so muchhh for this! I haven't notice this kind of scenario.
The "Previous Resolved 2" is the correct logic. 😄
Is it also possible that we can convert the result of Previous Resolved 2 = Latest (Ticket_Created_Time_Date) - Previous(Resolve_Time_Date) as duration? (Ex. 9 days 21:03:00 - DD HH:MM:SS)
Thank you so much in advance! 🙂
Hello @icdns
We can, yes. Enter this as a calculated column in your table.
Miniumum Time Before Failure = VAR CurrentResolve = 'Table'[ResolveTime/Date] VAR CurrentCreated = 'Table'[Ticket Created Time/Date] VAR PreviouseResolved = CALCULATE( MAX ( 'Table'[ResolveTime/Date] ), ALL ( 'Table' ), 'Table'[ResolveTime/Date] < CurrentResolve, 'Table'[Ticket Created Time/Date] < CurrentCreated ) VAR NumDays = DATEDIFF( PreviouseResolved,'Table'[Ticket Created Time/Date], DAY ) VAR DaysReturn = SWITCH ( TRUE(), NumDays = 0, BLANK(), NumDays = 1, NumDays & " day ", NumDays > 1, NumDays & " days " ) RETURN DaysReturn & FORMAT ( 'Table'[Ticket Created Time/Date] - PreviouseResolved, "hh:mm:ss")
Hi @jdbuchanan71 ,
I tried creating the calculated column given your dax. Minimum Time before failure is not showing correctly..
Is this because i have filter "Call Priority?"
But when I created a table just for the "Priority 1" items, the correct values shows..
Thanks you.
Should only calls with the same priority be used when deterimining the previous resovled date and time? If so we just need to read it for the current row the use it as a filter in the PreviousResolved VAR:
Miniumum Time Before Failure = VAR CurrentResolve = 'Table'[ResolveTime/Date] VAR CurrentCreated = 'Table'[Ticket Created Time/Date] VAR CurrentPriority = 'Table'[Call Priority] VAR PreviouseResolved = CALCULATE( MAX ( 'Table'[ResolveTime/Date] ), ALL ( 'Table' ), 'Table'[ResolveTime/Date] < CurrentResolve, 'Table'[Ticket Created Time/Date] < CurrentCreated, 'Table'[Call Priority] = CurrentPriority ) VAR NumDays = DATEDIFF( PreviouseResolved,'Table'[Ticket Created Time/Date], DAY ) VAR DaysReturn = SWITCH ( TRUE(), NumDays = 0, BLANK(), NumDays = 1, NumDays & " day ", NumDays > 1, NumDays & " days " ) RETURN DaysReturn & FORMAT ( 'Table'[Ticket Created Time/Date] - PreviouseResolved, "hh:mm:ss")
Hi, @jdbuchanan71
Just a quick question, can I convert my "Minimum Time before Failure" column to number? Because I want to see its average..
Can I just simply use the modelling part view in PBI Desktop? 🙂
Thank you! 😄
You can, yes. This will give you the number of minutes.
Miniumum Time Before Failure Minutes = VAR CurrentResolve = 'Table'[ResolveTime/Date] VAR CurrentCreated = 'Table'[Ticket Created Time/Date] VAR CurrentPriority = 'Table'[Call Priority] VAR PreviouseResolved = CALCULATE( MAX ( 'Table'[ResolveTime/Date] ), ALL ( 'Table' ), 'Table'[ResolveTime/Date] < CurrentResolve, 'Table'[Ticket Created Time/Date] < CurrentCreated, 'Table'[Call Priority] = CurrentPriority ) VAR NumMinutes = DATEDIFF( PreviouseResolved,CurrentCreated, MINUTE ) RETURN NumMinutes
I would say that you don't need to go down to the second when running an average over something that can span many days.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |