Get PrevMember of Aggregate set of Fix Versions

Questions & Answers
1

Hi all,

I have a calculated member of the Fix Version dimension that is just an aggregate of a few specific Fix Versions of interest.

e.g.

My Calculated Version List = Aggregate({
  [Fix Version].[My Project].[Unreleased].[My Release 1],
  [Fix Version].[My Project].[Unreleased].[My Release 2],
  [Fix Version].[My Project].[Unreleased].[My Release 3],
  [Fix Version].[My Project].[Unreleased].[My Release 4]})

How do a refer to the previous item in the list inside a measure? I’m still at the early stages of experimentation here but I’m not able to reference within this list using [FixVersion].CurrentHierarchyMember.PrevMember or .Lag(x). Those functions seem to parse the project ‘All Fix Versions’ list and not stay within ‘My Calculated Version List’.

I can’t seem to use Lag or PrevMember under [Fix Version].[My Calculated Version List] either so I’m a bit lost at the moment.

Any ideas or thoughts on this are greatly appreciated. :slight_smile:

2

Hi @chrispy35,

The main difficulty here is that aggregated calculated members do not create “family bounds” between members in the set. Once you address children members from the aggregated set, you no longer see the calculated member as their parent or other members within the calculated set as closer siblings than those not in the calculated set.

Therefore, functions like .PrevMember or .Lag(x) or .Parent or .NextSibling do not work.

Some kind of workaround is possible by not breaking down the set and using Rank() function to rank the members of pre-defined calculated set and address the previous or the next one within the explicitly named set.

However, that needs additional conditional checks to avoid running into non-existing members of the set.
Also, while addressing the members with .Item() you need to remember that the first item on the list is .Item(0) but it has the rank of 1.

First, you might define a pre-calculated set of members named “My Calculated Version List”.

The expression to find the name of the previous member within the set might then be as follows.

CASE WHEN
--current member is not the first
 Rank(
  [Fix Version].CurrentMember,
   ChildrenSet([Fix Version].[My Calculated Version List]))>1
THEN
 ChildrenSet([Fix Version].[My Calculated Version List]).Item(
  Rank(
   [Fix Version].CurrentMember,
   ChildrenSet([Fix Version].[My Calculated Version List])
--the .Name part in next line retrieves the name of the found previous member 
  )-2).Name
END

If you need to use a measure in combination with that member, you might use it in a tuple or in a Sum together with the relevant measure.
A similar approach could be used to find the next member in the calculated list.

Please see below the expression to find the number of issues relevant for the next Fix version from the calculated member list.

CASE WHEN
--current member is not the last
 Rank(
  [Fix Version].CurrentMember,
   ChildrenSet([Fix Version].[My Calculated Version List]))<
 Count(ChildrenSet([Fix Version].[My Calculated Version List]))
THEN
 Sum(
 ChildrenSet([Fix Version].[My Calculated Version List]).Item(
  Rank(
   [Fix Version].CurrentMember,
   ChildrenSet([Fix Version].[My Calculated Version List])
  )),
  [Measures].[Issues created])
END

If you have multiple non-overlapping sets, you might also use the following construction to first determine if a current member belongs to a specific set and then act accordingly.
The construction might look as follows.

CASE
 WHEN
  Rank(
  [Fix Version].CurrentHierarchyMember,
 Childrenset([Fix Version].[Set 1]))>0
 THEN
--expression for explicitly defined set 1

 WHEN
  Rank(
   [Fix Version].CurrentHierarchyMember,
   Childrenset([Fix Version].[Set 2]))>0
  THEN
--expression for explicitly defined set 2

--additional options
END

Regards,
Oskars / support@eazyBI.com