Simplify Measures

Questions & Answers
1

Hi eazyBI genuises,

I am currently trying to calculate Aging. However, this measure is taking too long to load because it is iterating through all the issues and get timeout. May I seek your opinion on how to simplify this measure. I only want the Aging measure for Story only.

–Refinement Aging

Avg(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
not IsEmpty([Measures].[Issue Refinement - Cycle current start date])
AND
– show for current period only. it will be the correct data
DateInPeriod(
“Today”, [Time].CurrentHierarchyMember
)
),
CASE WHEN [Measures].[Issues in Refinement - Cycle] > 0
THEN
[Measures].[Issue Refinement - Cycle days] +
DateDiffDays([Measures].[Issue Refinement - Cycle current start date], now())
END
)

–Dev Aging

Avg(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
not IsEmpty([Measures].[Issue Development - Cycle current start date])
AND
– show for current period only. it will be the correct data
DateInPeriod(
“Today”, [Time].CurrentHierarchyMember
)
),
CASE WHEN [Measures].[Issues in Development - Cycle] > 0
THEN
[Measures].[Issue Development - Cycle days] +
DateDiffDays([Measures].[Issue Development - Cycle current start date], now())
END
)

– SIT Aging

Avg(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
not IsEmpty([Measures].[Issue SIT - Cycle current start date])
AND
– show for current period only. it will be the correct data
DateInPeriod(
“Today”, [Time].CurrentHierarchyMember
)
),
CASE WHEN [Measures].[Issues in SIT - Cycle] > 0
THEN
[Measures].[Issue SIT - Cycle days] +
DateDiffDays([Measures].[Issue SIT - Cycle current start date], now())
END
)

–UAT Aging

Avg(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
not IsEmpty([Measures].[Issue UAT - Cycle current start date])
AND
– show for current period only. it will be the correct data
DateInPeriod(
“Today”, [Time].CurrentHierarchyMember
)
),
CASE WHEN [Measures].[Issues in UAT - Cycle] > 0
THEN
[Measures].[Issue UAT - Cycle days] +
DateDiffDays([Measures].[Issue UAT - Cycle current start date], now())
END
)

2

Hi,

The options for possible optimizations depend on the report context. Please contact support and send the full report definitions to support for more detailed assistance.

There is one obvious optimization, however. The condition that enables the result for the current time period should be put outside the Filter function:

CASE WHEN
DateInPeriod(
  “Today”, [Time].CurrentHierarchyMember
)
THEN
Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    not IsEmpty([Measures].[Issue Refinement - Cycle current start date])
    -- show for current period only. it will be the correct data
  ),
  CASE WHEN [Measures].[Issues in Refinement - Cycle] > 0
  THEN
  [Measures].[Issue Refinement - Cycle days] +
  DateDiffDays([Measures].[Issue Refinement - Cycle current start date], now())
  END
)
END

Kindly,
Janis, eazyBi support