Hi,
We have the following setup
We have a measure called “Total Caused Hours Spent” which uses another measure called “Caused Hours Spent”. The Causes hours spent is meant to sum up the hours spent issues that are caused by a parent issue. This works as intended. We then needed to create this second measure “Total Caused Hours Spent” so that the previous measure could be totaled up. This works as seen by the 58.50, but the hours are the same on all issue types. We would like it to filter and total up based on issue type.
So if of the 58.50 hours spent in the project, Creative Print had 30 of those caused hours spent, we would like that reflected.
Below are our measure definitions
Also “Caused issues” is an imported link
I think you just need to remove the [Issue Type].DefaultMember from the Caused Hours Spent formula
Hi @Matthew_Feldman ,
Make sure that you have imported “Caused issues” also as a dimension (defined via advanced settings).
Then create the calculated measure using this code:
Case
When
[Issue].CurrentMember.Level.Name = "Issue"
AND
[Caused issues].Currentmember is [Caused issues].Defaultmember
Then
-- retrieve Bugs information from Issue property Caused issues
Case when
not isEmpty([Issue].CurrentHierarchyMember.Get('Caused issues'))
then
NonZero(SUM(
[Issue].[Issue].GetMembersByKeys(
[Issue].CurrentHierarchyMember.get('Caused issues')),
DefaultContext((
[Measures].[Hours spent],
[Issue].CurrentMember,
[Time].CurrentHierarchyMember,
[Logged by].CurrentMember))
))
end
Else
-- total calculation for any issue, data on Caused issues level
NonZero(SUM(
Filter(
Descendants([Caused issues].Currentmember, [Caused issues].[Caused issues]),
-- filter out Caused issues with reference to Issue
([Measures].[Issues created],
[Logged by].Defaultmember,
[Time].CurrentHierarchy.Defaultmember) > 0),
-- search for Caused issues in Issue dimension, use in a tuple with measure, logged by and time, ignore anything else with DefaultContext
DefaultContext((
[Measures].[Hours spent],
[Issue].[Issue].GetMemberByKey([Caused issues].Currentmember.Key),
[Logged by].CurrentMember, -- allow selection by Logged by
[Time].CurrentHierarchyMember, -- allow selection by Time
[Caused issues].DefaultMember
))
))
End
Martins / eazyBI support
2 Likes
Thank you so much, this worked perfectly