How to optimize my Count filter?

Questions & Answers
,
1

Hi , i got a working measure using count filter but it’s way to slow.
I try to limit the set by making a first filter before checking the value on a custom measure.

here is my code :

-- Compare la date de transition à acceptance 
--avec la date de fin de sprint
-- autheur : XHT


Count(
-- we used a first filter on issuetype to gain performance and avoid to calculate measure fo each issues


     Filter( cache(
           Filter( Descendants([Issue].CurrentMember, [Issue].[Issue]),
              -- filters issues where measure En retard sur le Sprint Cible = 1
                    [Measures].[Issue type] = 'Feature' 
                    OR 
                    [Measures].[Issue type] = 'Enabler Feature'
                    AND 
                    [Measures].[Issue PI]= [PI].CurrentHierarchyMember.Name
                    AND 
                    [Measures].[Issue Sprint cible]= [Sprint cible].CurrentHierarchyMember.Name)
          ),
          -- filters issues where measure En retard sur le Sprint Cible = 1
          [Measures].[En retard sur le Sprint Cible] = 'OK'

          
        )
        
     )
2

Hi @XavierH

The custom measure is slow mainly because it relies on the resource-heavy Descendants function that has to iterate through all the issues in the Issues and check each one of them separately against the criteria.

In this case, I would recommend using a measure like “Issues created” from the Measures dimension and using the “Issue Type”, “PI”, “Sprint Cible” dimensions in the Pages section to filter by the necessary types and PIs.

You can also import the “En retard sur le Sprint Cible” custom field as a dimension and include that in the Pages section as well, so you can select the “OK” value in the filter menu.

This way the report will only use the “Issues created” measure to return the count of the issues and will filter the number by the filters you have set in the Pages section.

​Let me know if this fits your use case or if you have any additional questions on this!
​Best regards,
​Nauris / eazyBI support