Help to optimize formula

Questions & Answers
1

Hello! I have the following formula that I’d like to optimize. I tried to do it with the help of AI, but the result wasn’t good. The formula currently returns the information well, but it’s very slow an reach 60 secods.

Report Description:
The objective of the report is to show all “Feature” type issues resolved in the current Q but also those resolved in the previous 3 Qs.

Reporte de Productividad
Reporte de Productividad1105×480 43 KB

Formula to Optimize

-- Optimized formula that returns the same results
CASE WHEN
  -- Check if there are any issues resolved with status "Finalizado" 
  -- in the quarter that is 2 quarters before current date
  (
    [Measures].[Issues resolved],
    [Status].[Finalizado],
    [Time].[Quarter].CurrentDateMember.Lag(2)
  ) > 0
THEN
  -- Calculate the sum directly with all conditions in one tuple
  Sum(
    Filter(
      DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      -- Direct filter condition without using IIF
      (
        [Measures].[Time to Market Delivery], 
        [Status].[Finalizado],
        [Time].[Quarter].CurrentDateMember.Lag(2)
      ) > 0.5
      AND
      (
        [Measures].[Time to Market Delivery], 
        [Status].[Finalizado],
        [Time].[Quarter].CurrentDateMember.Lag(2)
      ) < 12
    ),
    (
      [Measures].[Issues resolved],
      [Status].[Finalizado],
      [Time].[Quarter].CurrentDateMember.Lag(2)
    )
  )
ELSE
  -- Return 0 if there are no issues resolved in the context
  0
END

Formula Time to Market Delivery. Within the main formula, use conditions on a measure called Time to Market Delivery

CoalesceEmpty(
CASE WHEN [Measures].[Issues resolved] > 0 THEN
  Avg(
    Filter(
      DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
      )
      AND 
      [Measures].[Issue status] = "Finalizado"
    -- AND [Measures].[Issue ¿Mide?] = '(none)'
    ),
    
    DateDiffDays(
      [Measures].[Fecha de Transición a Pendiente], 
      [Measures].[Issue resolution date]
    )
  )*24/720
END
,0)```

Thanks!
2

@Emiliano_Romero

You could try this optimized code:

“Time to Market Delivery”

Cache(
CASE 
WHEN [Measures].[Issues resolved] > 0 
THEN

  CASE WHEN
  [Issue].CurrentHierarchyMember.level.name = "Issue"
  THEN
    IIF(
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
      )
      AND 
      [Measures].[Issue status] = "Finalizado",
     DateDiffDays(
      [Measures].[Fecha de Transición a Pendiente], 
      [Measures].[Issue resolution date]
    )*24/720,
    0
    )
  ELSE
  Avg(
    Filter(
      DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
      )
      AND 
      [Measures].[Issue status] = "Finalizado"
    -- AND [Measures].[Issue ¿Mide?] = '(none)'
    ),
    
    DateDiffDays(
      [Measures].[Fecha de Transición a Pendiente], 
      [Measures].[Issue resolution date]
    )
  )*24/720
  END
END
)

Create new calculated member in “Time” dimension with aggregate “Last 4 quarters”

Aggregate({
  [Time].[Month].CurrentDateMember.Lag(3):
  [Time].[Month].CurrentDateMember.Lag(0)
})

And then the Final calculation as follows:

-- Optimized formula that returns the same results
CASE WHEN
  -- Check if there are any issues resolved with status "Finalizado" 
  -- in the quarter that is 2 quarters before current date
  (
    [Measures].[Issues resolved],
    [Status].[Finalizado],
    [Time].[Last 4 quarters]
  ) > 0
THEN
  -- Calculate the sum directly with all conditions in one tuple
  Sum(
    Filter(
      Filter(
        DescendantsSet(
          [Issue].CurrentHierarchyMember,
          [Issue].[Issue]
          ),
        [Measures].[Issue status] = "Finalizado"
        AND
        DateInPeriod(
          [Measures].[Issue resolution date],
          [Time].[Last 4 quarters]
        )
      
      ),
        [Measures].[Time to Market Delivery] > 0.5
      AND
        [Measures].[Time to Market Delivery] < 12
    ),
    (
      [Measures].[Issues resolved],
      [Status].[Finalizado],
      [Time].[Last 4 quarters]
    )
  )
ELSE
  -- Return 0 if there are no issues resolved in the context
  0
END

If you still find it slow, please contact support@eazybi.com and provide the full report definition.