EazyBI Formula is not valid error

[TN Raju]

I am building a Average Age of Working Items in progress report. 

I have created the below Measures: 

While creating Total Work Item Age Measure using below formula & getting the error

Aggregate(
Filter(
[Issue].[Status].Members,
[Issue].CurrentMember.Name = "In Progress"
),
[Measures].[ Work Items Age in Days]
)

Please Suggest

[Gerda Grantiņa · Accepted solution]

Hi @TN Raju ,

If you want to filter your issues by some property and then calculate the average days, you need to use the DescendantsSet() function. 

Try this formula:

NonZero(Avg(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Issue status] = "In Development"
    AND
    ([Measures].[Transitions to status],
    [Transition Status].[In Progress],
    [Time].CurrentHierarchy.DefaultMember) > 0
  ),
  [Measures].[Work Items Age in Days]
))

best,
Gerda

[TN Raju]

Thanks. its worked.

[TN Raju]

.