"OR" condition from different dimensions in Rows

[omasoud]

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

graph.png

[Martins Vanags -eazyBI- · Accepted solution]

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]

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.