Need help on EazyBI formulas

Hi Team,

We recently added a new SLA to a project, but it doesn’t apply to closed/resolved tickets, so we are trying to calculate it in EazyBI, here’s what I’ve got now, kindly need your help.

Column "[tt] First Response (d)"
It displays the minimum value of the set “(ticket first gets assigned time), (status transitioned to In Progress time), (Status transitioned to Canceled time)”, it works well

Column "[tt] Met (<1d)"
Same concept as the built-in member “(SLA) - Met”, I want it to show:
1: for First Response day <= 1 day
Empty: for First Response day > 1 day

Till now, it’s working nicely, but if you look at the top 2 rows “All Issues” and “People and Culture Hub - Test”, they show nothing, I can’t find a way to make it just like the built-in member “Time to resolution - Met” to show the subtotal (for example in this case, they should both show 2 instead of nothing).

Can you please help with that? also, we need the formula for the Met % as well, thank you!

My Code for [tt] First Response (d)

NonZero(
  Avg(
    Filter(
      Descendants([Issue].CurrentMember,[Issue].[Issue]), [Measures].[Issues created] > 0
    ),
    CASE WHEN
      IsEmpty(CASE WHEN
        DateDiffMinutes(
          [Issue].CurrentHierarchyMember.get('Created at'), TimestampToDate([Measures].[Transition from status first timestamp])
        ) < 
        DateDiffMinutes(
          [Issue].CurrentHierarchyMember.get('Created at'), 
          TimestampToDate(
            ([Transition field].[Assignee],
            [Measures].[Transition to first timestamp],
            [Time].CurrentHierarchy.DefaultMember)
          )
        )
      THEN
        DateDiffWorkdays(
          [Issue].CurrentHierarchyMember.get('Created at'), TimestampToDate([Measures].[Transition from status first timestamp])
        )
      ELSE
        DateDiffWorkdays(
          [Issue].CurrentHierarchyMember.get('Created at'), 
          TimestampToDate(
            ([Transition field].[Assignee],
            [Measures].[Transition to first timestamp],
            [Time].CurrentHierarchy.DefaultMember)
          )
        )
      END)
    THEN
      DateDiffWorkdays([Issue].CurrentHierarchyMember.get('Created at'), now())
    ELSE
      CASE WHEN
        DateDiffMinutes(
          [Issue].CurrentHierarchyMember.get('Created at'), TimestampToDate([Measures].[Transition from status first timestamp])
        ) < 
        DateDiffMinutes(
          [Issue].CurrentHierarchyMember.get('Created at'), 
          TimestampToDate(
            ([Transition field].[Assignee],
            [Measures].[Transition to first timestamp],
            [Time].CurrentHierarchy.DefaultMember)
          )
        )
      THEN
        DateDiffWorkdays(
          [Issue].CurrentHierarchyMember.get('Created at'), TimestampToDate([Measures].[Transition from status first timestamp])
        )
      ELSE
        DateDiffWorkdays(
          [Issue].CurrentHierarchyMember.get('Created at'), 
          TimestampToDate(
            ([Transition field].[Assignee],
            [Measures].[Transition to first timestamp],
            [Time].CurrentHierarchy.DefaultMember)
          )
        )
      END
    END  
  )
)

My Code for [tt] Met (<1d)

SUM(
  Filter({[Measures].[[tt]] First Response (d)]} ,[Measures].[[tt]] First Response (d)] <= 1),
  1
)

Hi @hsu1 ,
First, you should add Cache() for your formula “[tt] First Response (d)” as it can be useful when the same calculation is repeated in the report more than once.
And then reuse it in the second formula in which you need to have again Descendants() with “Issue” dimension.

NonZero(
  Count(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[[tt]] First Response (d)] < 1
    )
  )
)

best,
Gerda // support@eazyBI.com

Hi Gerda,

I’ve figured out how to accomplish my requirement myself, you can close this ticket, thanks.