Hello, Community. I need your help. I am trying to create a graph with bars representing created and resolved issues. Additionally, I want to add lines for the average, median, and hours spent. I am using the standard ‘Hours Spent’ measure, and for the average and median, I am using the simple script below:
Median(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Hours spent] > 0
),
[Measures].[Hours spent]
)
But I have two problems:
I need the ability to choose a specific user by pages. However, if I add the assignee to the pages, it calculates all hours spent for issues assigned to the chosen user, even if it was logged by other users.
If I add ‘Logged by’ to the pages, it calculates hours spent, average, and median correctly. However, it no longer calculates created and resolved issues (empty cells).
When using ‘Logged by’
Could you please advise me on how to achieve accurate calculations for hours spent while still including created and resolved issues on the same graph or table?
Thank you.
Since Assignee and Logged by dimensions usually contain the same list of users, this formula feeds the Logged by user key to the Assignee dimension in order to return issues that are assigned to that user.
You can create a similar calculation by changing the measure to “Issues resolved”.
Thank you for your reply. The formula you provided offers a good solution with correct data. I have encountered another issue: it works well with individual users, but when I choose ‘All Users’ in the list, it stops calculating and shows empty cells in the row. Do you have any suggestions on how to make it universally work, both when I choose one separate user and when I choose ‘All Users’ for the total statistics of the company? I would greatly appreciate your assistance.
Default members are not named the same (“All Users” vs “All Assignees”), therefore the formula needs to be altered to process them differently:
CASE WHEN
[Logged by].CurrentHierarchyMember IS [Logged by].CurrentHierarchy.DefaultMember
THEN
NonZero(
([Measures].[Issues created],
[Assignee].DefaultMember
))
ELSE
NonZero(
([Measures].[Issues created],
[Assignee].[User].GetMemberbyKey([Logged by].CurrentMember.Key),
[Logged by].DefaultMember))
END
