I have a request for writing the calculated measure which is like:
AVG(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
not IsEmpty([Issue].CurrentHierarchyMember.get(‘Resolved at’))
AND
DateInPeriod(
[Issue].CurrentHierarchyMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember
)
)
,
DateDiffWorkdays(
[Issue].CurrentHierarchyMember.get(“plan start date”),
[Issue].CurrentHierarchyMember.get(‘plan end date"’)
)
)
But I have a custom field like “department” used in the page, so I need the measure can be calculated based on the “department” selection. How should I write the script? Thanks a lot.
You are on the right track! The problem is that there is no measure in the filtering options. Measure is a treasure - binding dimension members together.
Please try the following
AVG(Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
NOT IsEmpty([Issue].CurrentHierarchyMember.get('Resolved at'))
AND
DateInPeriod(
[Issue].CurrentHierarchyMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember
)
AND
[Measures].[Issues resolved] > 0
),
DateDiffWorkdays(
[Issue].CurrentHierarchyMember.get('plan start date'),
[Issue].CurrentHierarchyMember.get('plan end date')
)
)
Thanks for your reply. It seems that you add ```
[Measures].[Issues resolved] > 0
in the filter formula. If I understand right, this measure is used for the dimension select, right? Thanks.
Yes, that is what I added. The Issues resolved is a measure. Measures show greater than zero where the issue matches the context - selected dimension values. At the same time properties return value whenever the Issue level is available, but do not care about other dimension contexts.