Hi,
I have two sets of fix versions in different hierarchies (printout obtained with SetToStr())
Set1:
“{[Fix Version].[Plan Engineering].[Unreleased].[(TC-v6.3r1p8-Candidate)], [Fix Version].[Plan Engineering].[Unreleased].[(TC-v6.3r1p8-MUST)]}”
Set2:
“{[Fix Version.By name].[(TC-v6.3r1p8-Candidate)]}”
Intersect of the two sets is empty although they both contain the element “(TC-v6.3r1p8-Candidate)”.
How can I translate the sets into the same hierarchy (so I can intersect them etc)?
Thx!
Sharing the email reply from eazybi as it solved my problem:
The Intersect() requires the sets to belong to the same hierarchy.
However, the actual idea behind the intersection of sets is to find their overlapping fraction.
The idea is to CrossJoin both sets and then retain the valuable combinations.
This could be done via the NonEmptyCrossJoin() function.
I created the sets of fix versions for the issue looking from different hierarchies.
Then I CrossJoined them in two ways.
As you can see - the NonEmptyCrossJoin returns the valuable combinations of versions.
In practice - every different hierarchy of the same dimension behaves as a separate dimension.
Please let me know if this might work for you.
So NonEmptyCrossJoin() somehow magically creates a set with the right elements, but the set behaves slightly different than a set that contains only one hierarchy. If you try to get Item(0).get(‘some property’) this fails, instead I had to get the property inside generate (instead of the Item(0)), then get() works.
Some more things I learned here:
It can happen that the NonEmptyCrossJoin returns pairs of different fix versions so if you want to intersect the sets, you need to filter those pairs where the name of the fix versions in both pair elements are the same and check if any pairs remain.
It was really not clear to me how to do this filtering (how to access the pair elements in the filter). Oskars from eazybi helped with this:
Generate(
--the issue set
Filter(
[Issue].[Issue].Members,
-- reduce from all issues to speed it up
Cast([Issue].CurrentHierarchyMember.Get('Assignee name') as string)
=
Cast([Logged by].CurrentHierarchyMember.Key as string)
--there is at least one fix version common to the issue and page selection
AND
-- this is the intersect of the 2 sets in different dimensions!!!:
Count(
Filter(
NonEmptyCrossJoin(
--fix versions of issue
[Fix Version].[Version].getMembersByKeys(
[Issue].CurrentHierarchyMember.get('Fix version IDs')),
ChildrenSet([Fix Version.By name].CurrentHierarchyMember)
),
--filter for the set of tuples returned by nonemptycrossjoin
-- !!!you can access the pair elements in the set like this!!!:
Cast([Fix Version].CurrentMember.Name as string)
=
Cast([Fix Version.By name].CurrentMember.Name as string)
)
)>0
),
--the string for each issue
--first - issue name
[Issue].CurrentHierarchyMember.Name
||":"||
-- this was just added to debug the filtering of pairs:
Generate(
--filter overlapping version sets
Filter(
NonEmptyCrossJoin(
--fix versions of issue
[Fix Version].[Version].getMembersByKeys(
[Issue].CurrentHierarchyMember.get('Fix version IDs')),
--fix versions from page selection
ChildrenSet([Fix Version.By name].CurrentHierarchyMember)
),
--filter for the set of tuples returned by nonemptycrossjoin
--the names in both hierarchies match
Cast([Fix Version].CurrentMember.Name as string)
=
Cast([Fix Version.By name].CurrentMember.Name as string)
),
--the string value for each relevant version
[Fix Version].CurrentMember.Name,
--split by comma
",")
,
--split by new line
CHR(10)
)
The extra complexity in the expression is caused by the background setup:
Cast([Issue].CurrentHierarchyMember.Get('Assignee name') as string)
=
Cast([Logged by].CurrentHierarchyMember.Key as string)
This condition led to a possible type mismatch, therefore both values were cast as strings.
The actual filtering of the set of tuples created by CrossJoin or NonEmptyCrossJoin could be as follows.
--filter overlapping version sets
Filter(
NonEmptyCrossJoin(
--fix versions of issue
[Fix Version].[Version].getMembersByKeys(
[Issue].CurrentHierarchyMember.get('Fix version IDs')),
--fix versions from page selection
ChildrenSet([Fix Version.By name].CurrentHierarchyMember)
),
--filter for the set of tuples returned by nonemptycrossjoin
--the names in both hierarchies match
[Fix Version].CurrentMember.Name
=
[Fix Version.By name].CurrentMember.Name)
Or, for a generic situation, you might put it this way.
--filter overlapping version sets
Filter(
NonEmptyCrossJoin(
--set of first dimension members
[Dimension1].[D1 level].Members,
--set of second dimension members
[Dimension 2].[D2 level].Members
),
--filter for the set of tuples returned by nonemptycrossjoin
--the names in both dimension members match
[Dimension 1].CurrentMember.Name
=
[Dimension 2].CurrentMember.Name
)
It is important to to specify the dimension hierarchy and use .CurrentMember when addressing the members to avoid unexpected surprises if the same dimension but a different hierarchy is used elsewhere in the expression or report.
regards,
Oskars / support@eazyBI.com
To simplify this you could consider to provide a function to your users that allows to lower sets by stripping away all the dimension stuff and only retain plain elements (no order no nothing…like a set in maths). Working with these would be much less complicated (no weird tuples with different dimensions like crossjoin) and you could just intersect these etc. Just an idea/feature request.