Optimize measures

Questions & Answers
1

Hi,

I have a graph that is super slown, sometime it is not display because it spend more than 60 seconds to compute. So, I’m looking for a way to optimize this. Any suggestion is welcome of course :wink:

Here is how I define it, I’m looking to count the number of tickets with an age between 60 and 90 working days.

Aggregate(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
CASE WHEN
[Measures].[Issues created] > 0
AND
– check if issue is relevant for the report
DateDiffWorkdays([Measures].[Issue created date], Now()) >= 60
AND
DateDiffWorkdays([Measures].[Issue created date], Now()) < 90
THEN
1
END
)

2

Here is what I would try to get the performance better…

Create a new measure with the DateDiffWorkdays([Measures].[Issue created date], Now()) for the value.

Then add that to the report and filter based on that value being between 60 and 90.

Once you have the filter added you can remove the measure from the report and the filter will stay.

1 Like
3

@cdemez
Try this formula:

SUM(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateDiffWorkdays([Measures].[Issue created date], Now()) >= 60
    AND
    DateDiffWorkdays([Measures].[Issue created date], Now()) < 90
    And 
    DateInPeriod([Measures].[Issue created date],[Time].CurrenthierarchyMember)
  ),
  [Measures].[Issues created]
)

Martins /eazyBI

1 Like
4

IT help a little bit, but it is still very slow :stuck_out_tongue:

5

Hi @cdemez

There is no good workaround to calculate the age in workdays.
If you could export and share the definition, perhaps there are some ways to add more filters to your custom calculation to slightly improve it

Age interval dimension would work much faster, but it will calculate the age on all days (not just workdays)
https://docs.eazybi.com/eazybi/analyze-and-visualize/interval-dimensions

Martins / eazyBI

1 Like