Filtering based on no of days past created date - performance issue

SR_k4fu6r

I have this code using descendants which works, but often run into query timeout issue. How can I improve this?

 

----------- Begin code ---------

/* Gives the total open bugs that have exceeded beyond the specified number of SLA days -
P0 = 1 day
P1 = 7 days
P2 = 90 days
TBD a.k.a. Untriaged bugs = 7 days */

CASE WHEN
[priority].CurrentMember.Name = "P0"
THEN
NonZero(
Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]), [Measures].[Open Bugs] > 0 AND
DateDiffDays([Issue].CurrentMember.Get('Created at'), Now()) > 1
)))

WHEN
([priority].CurrentMember.Name = "P1" OR [priority].CurrentMember.Name = "TBD")
THEN
NonZero(
Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]), [Measures].[Open Bugs] > 0 AND
DateDiffDays([Measures].[Issue created date], Now()) > 7
)))

WHEN
[priority].CurrentMember.Name = "P2"
THEN
NonZero(
Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]), [Measures].[Open Bugs] > 0 AND
DateDiffDays([Measures].[Issue created date], Now()) > 90
)))
END

-------- END CODE ---------

Also, does using [Issue].CurrentMember.Get('Created at') over [Measures].[Issue created date] have any performance impact? 

[Measures].[Issue created date] is a user defined measure for the created at.

 

 

thanks in advance,

SR

 

Fabian Lim

Hi @SR

I would suggest creating separate reports and separate measures for each condition. Your formula is too heavy since you using descendants function.  

Then put them into one dashboard as counters for each report.

Regards