Hi, EazyBI team.
I’d like to ask for help to create Measure.
Based on our SLA, we want to count tickets according to resolution days/time.
Could you check and correct If I am wrong?
Please review measure below.
Thanks
[ Criteria ]
. Resoluion Days/Time : Ticket Closed - Ticket Created
. Status : CLOSED
. We want to show chart based on ticket creation time (x-axis)
e.g) First chart, we want to count tickets which created in W33 and status is CLOSED
and resolution days/time is ~4h, 4~24hr, 24~36hr, 36hr~
- [Measures].[~4hr]
–annotations.group=LeadTime Overview
– ClosedTime ~4hr
CASE WHEN [Measures].[Issues resolved] > 0 THEN
NonZero(Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod([Issue].CurrentHierarchyMember.get(‘Created at’),
[Time].CurrentHierarchyMember) AND
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0 AND
DateDiffDays(
[Issue].CurrentHierarchyMember.get(‘Created at’),
[Issue].CurrentHierarchyMember.get(‘Closed at’)) <= (1/6) AND
[Measures].[Issue status] = [Status].[Closed].Name
)
))
END
- [Measures].[4~24hr]
–annotations.group=LeadTime Overview
– ClosedTime 4~24hr
CASE WHEN [Measures].[Issues resolved] > 0 THEN
NonZero(Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod([Issue].CurrentHierarchyMember.get(‘Created at’),
[Time].CurrentHierarchyMember) AND
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0 AND
DateDiffDays(
[Issue].CurrentHierarchyMember.get(‘Created at’),
[Issue].CurrentHierarchyMember.get(‘Closed at’)) > (1/6) AND
DateDiffDays(
[Issue].CurrentHierarchyMember.get(‘Created at’),
[Issue].CurrentHierarchyMember.get(‘Closed at’)) <= 1 AND
[Measures].[Issue status] = [Status].[Closed].Name
)
))
END