I have two custom fields in Jira - “Obligatory for” and “Participated”, where users are listed. I would like create calculated measure “Did not participated” where I could compare and print out users missing in “Participated” field.
I tried to achieve it by using Aggregate and Except function, but all I could get is number which showd diff of users.
Can anyone point to right direction. Thanks.
Hi @ragnarveermae
It seems that both your jira custom fields are user-picker (multiple users) fields to support the idea of having multiple users added to the issue.
In that case, make sure you imported both fields as dimensions and properties
Then you could create a calculated measure “Did not participate” using this formula where you need to address correct properties and dimensions. See the attachment below with my example.
Generate(
Filter(
[Approvers].[Approvers].members,
DefaultContext((
[Measures].[Issues created],
[Approvers].CurrentMember,
[Issue].CurrenthierarchyMember
))>0
AND
Not [Measures].[Issue Change managers]
matches ".*"||[Approvers].CurrentMember.name||".*"
),
[Approvers].CurrentMember.name,
chr(10)
)
To show imported issue properties for both user-picker fields as list (instead of comma separated string), you could use chr(10) as separator and function Replace (see the calculated measure “Approvers” in my screenshot below)
Replace([Measures].[Issue Approvers],",",CHR(10))
Martins / eazyBI
Thank you for your solution, it worked with one case of mine, but is there somekind of easy solution if you want to substract one list (Calculated measure) from second list (Calculated measure)?
Unfortunately, there is no easy solution to compare 2 multi-value lists.
And, the formula I shared earlier should subtract the second list from the first already.
Martins
