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

SR March 6, 2020

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

 

1 answer

0 votes
Fabian Lim
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
December 5, 2021

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

Suggest an answer

Log in or Sign up to answer