Trying optimize Measure involving multiple tuples. Any idea will be appreciated

Questions & Answers
, ,
1

I’ve been asked to create a metric that can display tickets escalated from Tier 1 support to Tier 2 support.
My though process is to create 3 tuples:

  1. Transition with original assignee Tier 1
( [Measures].[Transitions from], 
  [Transition Field].[Assignee],
  [Assignee].[IT Tier 1])
  1. Transition with target assignee Tier 2
( [Measures].[Transitions to], 
  [Transition Field].[Assignee],
  [Assignee].[IT Tier 2])
  1. Tickets current assignee is Tier 2
    [Measures].[Resolved by Tier2(Assignee)] = ([Assignee].[IT Tier 2],[Measures].[Assignee])
Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    NOT IsEmpty([Measures].[Resolved by Tier 2(Assignee)])
  ),1
)
  1. When the above 3 tuples all return 1. Means that’s an escalation T1 to T2 occur
Sum(
  Filter(
    Descendants(
      [Issue].CurrentHierarchyMember,[Issue].[Issue]
    ),
  [Measures].[Transition from Tier 1]>0 AND
  [Measures].[Transitions to Tier 2]>0 AND
  [Measures].[Resolved by Tier 2]>0
  ),1
)

So this measure gives me what I want. But when I do drill across Component Dimension. It is taking forever to execute and timeout pretty frequent.
I have a feeling that 3 tuples is way too complex. Plus component is another complex dimension. But I cannot think a way to reduce complexity for the 3 tuples at the moment.

Any idea will be much appreciated.

2

Hello QiZhang,
Welcome to eazyBI community!

There seems to be some part of the calculation missing for tuple 3, so I’m not 100% sure, but you could try the following formula:

sum(
Filter(Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
([Measures].[Issues resolved],[Assignee].[IT Tier 2])>0),
CASE WHEN
([Measures].[Transition from first timestamp],
[Transition Field].[Assignee],
[Assignee].[IT Tier 1])
=
([Measures].[Transition to first timestamp],
[Transition Field].[Assignee],
[Assignee].[IT Tier 2] )
THEN 1
END
)

Let us know if that works for you, and if not - add your report definition here or send your question to support@eazybi.com to discuss it in more detail.

Kindly,
Ilze

1 Like
3

Thank you so much!
I think the overall calculation time improved.
Thank you.