"OR" condition from different dimensions in Rows

omasoud May 3, 2016

I'm trying to create a timeline graph that plots for each day , the total number of open up to that date. The graph (which uses in the columns: [Measures].[Issues in transition status] and [Transition Status].[Open] and some other states) is displaying fine (see attached image). Where I'm having issues is in the query I want in order to limit to which issues I want to be included.

I want only defects that have either fix version = 7.3 or affects version = 7.3.

To the rows, I added, in addition to Time dimension so I can get days, an Issue Type dimension (with "Defect" via search and bookmark), a Fix Version dimension (with "7.3" via search and bookmark) and an Affects Version dimension (with "7.3" via search and bookmark). That, as expected, gave me "fix version = 7.3 and affects version = 7.3" which isn't what I want.

I removed Affects Version dimension and added a calculated member within the Fix Version dimension = [Fix Version.By name].[7.3] + [Affects Version.By name].[7.3]. But that double counted the items that have both fix and affects version set to 7.3.

It seems like a simple solution should be possible but I can't figure it out (attempted in vain concocting something via crossjoins, unions, etc.)

graph.png

1 answer

1 accepted

1 vote
Answer accepted
Sir Mārtiņš Vanags
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 9, 2016

Hi Osama,

I can suggest you to create a new calculated measure 'Defects status total for 7.3' by using your measure 'Defects Status total' in tuples with Affects and Fix Versions, by using this formula:

([Fix version.By name].[7.3],
[Measures].[Defects Status total]
+ 
([Affects version.By name].[7.3],
[Measures].[Defects Status total])
- (
[Fix version.By name].[7.3],
[Affects version.By name].[7.3],
[Measures].[Defects Status total]
)

After that you should remove "Fix Versions" dimension from rows as you are creating new calculated measure that counts issues from Fix or Affects version.


Please follow this link to find more about creating tuples: https://docs.eazybi.com/display/EAZYBI/Calculated+members#Calculatedmembers-Tuples


Kind regards,

Martins Vanags / support@eazybi.com

omasoud May 9, 2016

Thanks. That worked great!

How can I generalize this (instead of one fix version and one affects version, let's say we have a list of fix versions and a list of affects versions)?

I tried defining [Fix Version].[Desired Versions] to be

Aggregate(Filter(
[Fix Version].Members,
[Fix Version].CurrentMember.Name = 'version 1' OR
[Fix Version].CurrentMember.Name = 'version 2' OR
[Fix Version].CurrentMember.Name = 'version 3' OR
...
))

and similarly defined [Affects Version].[Desired Versions]. Then I rewrote your formula:

([Fix version].[Desired Versions],
[Measures].[Defects Status total])
+ 
([Affects version].[Desired Versions],
[Measures].[Defects Status total])
- (
[Fix version].[Desired Versions],
[Affects version].[Desired Versions],
[Measures].[Defects Status total]
)

But that ran out of memory.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events