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