Hi @cdemez
Indeed, dealing with multi-value fields might get tricky.
There are options for distinguishing issues with one or several values in a multi-value field. Choose one that suits your needs better.
1. Calculated measures with a filter by partners
If you need this distribution occasionally, you may include the filter directly in the measure.
For your case, you would create two custom measures (in Measures) that iterate through issues checking their property “Issue Partner” value and suming up transitions for issues with one / multiple partners.
To get issues with multiple partners, it would look for issues having the symbol “,” in the property value (it means there is a list of several partners like “BL.be,ZA.co”); vice versa for single partners (“CA.be”).
The calculation formula for a measure counting transitions from issues with multiple values:
Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
Not ([Measures].[Issue Partner] = "(none)")
AND
-- issue has a list of partners
[Measures].[Issue Partner] Matches ".*,.*"
),
[Measures].[Transitions to status issues count]
)
Calcuation formula for counting transitions from issues with one partner:
Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
Not ([Measures].[Issue Partner] = "(none)")
AND
--issue has one partner
Not [Measures].[Issue Partner] Matches ".*,.*"
),
[Measures].[Transitions to status issues count]
)
Check property “Issue Partner” name!
2. Calculated members in Partner CSV dimension
Another option, if you need this distribution more frequently: you may import Partner CSV dimension. This CSV dimension would contain all partner combinations existing for imported issues.
See examples of how to import CSV dimensions (with advanced settings):
- Filter out issues that contain 1 value ina multi-selct field - #2 by CarlyBooth
- Filter JIRAs with two labels - #2 by daina.tupule
Then you would create two calculated members in the “Partners CSV” dimension to aggregate members with one and members with multiple values, using the same filter by the existence of the symbol “,” in the member name.
In the report, you would use those calculated members in the report with any measure to distribute
The formula for calculated member containing single partner:
Aggregate(
Filter(
[Partner CSV].[Partner CSV].Members,
[Partner CSV].CurrentMember.Name <> "(none)" and
not [Partner CSV].CurrentMember.Name matches ".*,.*")
)
Calculated memebr containing members for multiple partners:
Aggregate(
Filter(
[Partner CSV].[Partner CSV].Members,
[Partner CSV].CurrentMember.Name <> "(none)" and
[Partner CSV].CurrentMember.Name matches ".*,.*")
)
Check dimension name!
Compare the final reports that are created using both approaches.
-
When you use two measures:
-
When you use CSV dimension (I used custom field Fruits instead of Partner):
Best,
Ilze, support@eazybi.com