I have created a custom measure that counts the number of tickets that planned to be open at any given time using the Due Date. I am trying to get this custom measure to dynamically filter on the selected Projects from the Pages. I have been unsuccessful and need some help getting dimensions in the Pages section to apply to my custom measures. Thanks!
Count(Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
– Due Date needs to be after the current Time
DateAfterPeriodEnd(
[Issue].CurrentMember.Get(‘Due date’),
[Time].CurrentHierarchyMember)
AND
– Created Date needs to be before the current Time
DateBeforePeriodEnd(
[Issue].CurrentMember.Get(‘Created at’),
[Time].CurrentHierarchyMember)
AND
–Filter out any unselected Projects from Pages
[Project].CurrentHierarchyMember MATCHES [Issue].CurrentMember.get(‘Project key’)
)
)
Hi @Chad_Schaeffer,
You might want to add a few improvements to the calculation so it would respond to the report context (selected values on pages, rows, and columns) and calculate the issue count accordingly.
-
Measure is the treasure! and defines how dimensions are related to each other. Add a predefined measure**, like “Issues created” or “Issues due”, as filter criteria. For example,
AND
--measure to bring report context,
--filter criteria that issue has a due date at any period
([Measures].[Issues with due date],
[Time].CurrentHierarchy.DefaultMember) > 0
Please see the ground rules for calculations here: Calculated measures and members
-
The syntax for the last filter criteria (Filter out any unselected Projects from Pages) is incomplete and would not ever be true.
When comparing two members with operand MATCHES, you might want to refer to the member name or key. For example:
[Project].CurrentHierarchyMember.KEY MATCHES
[Issue].CurrentHierarchyMember.get('Project key')
As an alternative solution, check out this “Issue due and overdue” report in the template account:
https://eazybi.com/accounts/1000/cubes/Issues/reports/249876-issues-due-and-overdue
Best,
Zane / support@eazyBI.com
Thank you very much. I was able to update the customer measure to get the filtering to work. It appears like we have to use a Root Member or a predefined measure to get the page filtering to apply. Is there any way to do it without using a predefined measure? I am asking because the measure I pasted before is the “Plan” part of a burndown plot and the “Actual” part needs to count all issues that are still open at any given day. I am using the [Issues Created] root member but worry it may not catch ALL open tickets.
[Measures].Plan:
Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
– filters out issues with due dates in the future
DateAfterPeriodEnd(
[Issue].CurrentMember.get(‘Due date’),
[Time].CurrentHierarchyMember)
AND
– filters out issues that were created in the future
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time]. CurrentHierarchyMember)
),
– count issues that match criteria
– measure will work as afilter to count only relevant issues for report selection
([Measures].[Issues with due date],
[Time].CurrentHierarchy.DefaultMember )
)
[Measures].Actual:
–Exclude days after today from graph
CASE WHEN
DateAfterPeriodEnd(“Today”, [Time].CurrentHierarchyMember)
OR
DateInPeriod(“Today”, [Time].CurrentHierarchyMember)
THEN
NonZero(Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
– filters out issues resolved in the future
DateAfterPeriodEnd(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember)
AND
– filters out issues that were created in the future
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time]. CurrentHierarchyMember)
OR
–Add in issues with blank resolution fields
IsEmpty([Issue].CurrentMember.Get(‘Resolved at’))
),
– count issues that match criteria
– measure will work as a filter to count only relevant issues for report selection
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember )
))
END