Filtering tickets based on 2 components

[Luke_Cauchi]

Hi, I am trying to calculate the time spent for tickets with specific components.

Individually calculating the time spent per component is easy but when I try to aggregate the time spent on tickets that have Component A, B or A and B I’m getting duplicates.

In short I have:

• Task 1 (Component A) Time Spent 1 hr
• Task 2 (Component B) Time Spent 1 hr
• Task 3 (Component A and B) Time Spent 1 hr

I expect the aggregate result to be 3 hrs
but instead I am getting 4hrs

This is the Calculated Member Formula I am using in the Project dimension

Count(Filter(
  [Project].[Component].Members, 
  [Project].CurrentMember.name MATCHES 'Component A'
  OR
  [Project].CurrentMember.name MATCHES 'Component B'
))

Any help is appreciated

[martins.vanags]

Hi @Luke_Cauchi

Try creating calculated measure using this formula:

Sum(
Filter(
DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Hours spent]>0
),
(
[Measures].[Hours spent],
[Project].DefaultMember
)*1
)

Note this calculated measure uses a complex function DescendantsSet to iterate through imported issues, therefore, report could get slower.

Martins / eazyBI