How to improve performance of the query used in calculated measure

martins.vanags · June 12, 2025, 11:46am

Hi,

Here is what you can try to do to calculate results much faster.

Create a new calcualted measure “issue set” using this formula:
This measure is unnecessary to select, but it must exist in the cube, with that specific name. It is caching set of issues with the issue type from each row and will be used in next calculation.

Cache(CASE WHEN
 NOT [Project].CurrentHierarchyMember is [Project].CurrentHierarchy.DefaultMember
 AND
 [Issue].CurrentHierarchyMember is [Issue].CurrentHierarchy.DefaultMember
THEN
--optimized iteration
Generate(
  Filter(
	--set of relevant issues
     Generate(
    --set of relevant projects
      Filter(
        [Issue].[Project].Members,
        DefaultContext(([Measures].[Issues created],[Issue].CurrenthierarchyMember,[Project].CurrenthierarchyMember))>0
      ),
    --set of issues in filtered projects
     DescendantsSet(
      [Issue].CurrentHierarchyMember,
      [Issue].[Issue])
     ),
   IIF([Issue Type].CurrentMember.Level.Name = "Issue Type", 
   [Measures].[Issue type] = [Issue Type].CurrentMember.Name, 1)
  ),
  Cast([Issue].CurrentMember.key as string),
  ","
  )
ELSE
--general iteration from issues
Generate(
  Filter(
    DescendantsSet(
        [Issue].CurrentMember,
        [Issue].[Issue]
      ),
   IIF([Issue Type].CurrentMember.Level.Name = "Issue Type", 
   [Measures].[Issue type] = [Issue Type].CurrentMember.Name, 1)
  ),
  Cast([Issue].CurrentMember.key as string),
  ","
  )
END
)

then use this formula for your final calculated measure.

Sum(
  Filter(
    [Issue].[Issue].GetMembersByKeys(
      [Measures].[issue set] --from step1
    ),
  [Measures].[Transitions to status]>1
  ),
[Measures].[Transitions to status]
)
/
Sum(
  Filter(
    [Issue].[Issue].GetMembersByKeys(
      [Measures].[issue set] --from step1
    ),
  [Measures].[Transitions to status]>1
  ),
[Measures].[Transitions to status issues count]
)

This should calculate final results much faster than your previous method.

Martins / eazyBI