Calculating the number of teams satisfying a certain criteria

Questions & Answers
, , ,
1

I have a requirement wherein I need to display the number of teams for which the cycle time for the stories is below 2 weeks , between 2-4 weeks and greater than 4 weeks. For this I had created a calculated member as below and the data was getting displayed correctly .

NonZero(Count(
Filter(
Descendants([Team].CurrentMember, [Team].[Team]),
[Measures].[Cycle Range] ="<2 weeks"

However , there were few changes in our team structure with some of the teams being removed . So i created a new calculated member within the team dimension named “X” which uses the aggregate function to collect the list of all the teams that are required. However , upon using the new calculated dimension on the measure above , it is not giving me the count of teams under various bracket.

Can you please help ?

2

Hi,

The Descendants function does not work with the calculated members, unfortunately.
The count of aggregated members can be obtained using the ChildrenSet function.

In your case, you might need to combine both functions to make the formula universal:

NonZero(Count(
  Filter(
   DISTINCT({Descendants([Team].CurrentMember,[Team].[Team]),
    ChildrenSet(
      [Team].CurrentMember
    )}),
  [Measures].[Cycle Range] ="<2 weeks"
  )
))

Kindly,
Janis, eazyBI support