Need help creating calculated member that allows me to exclude some components

Questions & Answers
1

Need help creating calculated member that allows me to exclude some components
Currently I have:

Aggregate(
Except(
[Project].[Bacon].Children,
Filter([Project].[Component].Members,
[Project].CurrentHierarchyMember.Name
MATCHES ‘Component Name’
)))

but this doesn’t seem to be successful in filtering out issues with this particular component set. Would appreciate any help here.

For example, I have components A B and C. Issue 1 has both A and B set as components. Issue 2 has A and C. Following your approach above I’ve selected A from the dropdown, but I also want to exclude all issues with component B. so in the report I only want to see issues 2 being displayed, but not issue 1. Does that make sense?

2

Hi!

Could you please clarify - if you have components A, B and C, and following issues:
issue 1 with components A B
issue 2 with components A C
issue 3 with components B C
issue 4 with component C
issue 5 with components A B C

When using Jira JQL component not in (“A”, “B”), only issue 4 would be returned. Or do you wish to filter out only the issues that have both A and B components (issue 1 and 5?) and return any other component combination - issue 2, 3 and 4 ?

Lauma / support@eazybi.com

3

Hi Lauma! We are looking to have issue 4 show up.
This is what i have so far:
Under the projects Calculated members

Aggregate(
Except(
[Project].[Component].Members,
{
[Project].[Component].[A],
[Project].[Component].[B]
}
))

But issues with A B C, still show up on the table.

4

Hi!

In this case, to get results from JQL component not in … you can create a calculated member in Project dimension that aggregates all components that you do not wish to see issues with (in this example issues without A and B components):

Aggregate(
  Filter([Project].[Component].Members,
  [Project].CurrentMember.Name MATCHES "A|B|\(no component\)"
  ))

And then create a new Measure that would display ‘All issues with no component in the list’ (assuming the name of Project dimension calculated member is “A B”)

NonZero(
  [Measures].[Issues created] -
  ([Project].[A B], 
  [Measures].[Issues created count])
)

Please let me know if you have further questions regarding this!
Lauma / support@eazybi.com

5

Hello Lauma, I’m actually trying to see all issues that dont have component’s A, B.

if you have components A, B and C, and following issues:
issue 1 with components A B
issue 2 with components A C
issue 3 with components B C
issue 4 with component C
issue 5 with components A B C

I only want to see issue 4 as a result.
Currently i am using:
Aggregate(
Filter([Project].[Component].Members,
[Project].CurrentMember.Name MATCHES “SET 2|SET 1”
))

But is it getting 0 results, because we have more than 20 other components with issues that do not have these components.

6

Correction: when i use

Aggregate(
  Filter([Project].[Component].Members,
  [Project].CurrentMember.Name MATCHES "A|B|\(no component\)"
  ))

I don’t get the list of other components and their issues, I just get (no component) but unable to see the other components for example SET 3, SET 4 is C and D in this case.

7

Hi!

What is the configuration of the report? Did you also create the Measure that counts the issues with no components in the aggregated list?

See example report - I don’t have the Project dimension used in the report, the measure is taking the group of components for calculating how many issues do not have the component in the list

eazyBI
eazyBI.png1758×1758 325 KB

Lauma / support@eazybi.com