Problem with filters in calculated measure

Questions & Answers
1

Hello,

I have a problem with my custom measure

This measure calculates the time workdays to solve the tickets, but subtracting the time in which it is in two specific transitions. The problem is that if from the report, I filter by IssueType, it is subtracting the time for which they go through those transitions but of any IssueType without applying the filter that I indicate outside the formula and I have had to put the filter inside the measeure to make it work.

What should I do to make it filter correctly without doing this? Thank you so much

Filter IssueType
Filter IssueType829×718 30.3 KB

Measure filter IssueType
Measure filter IssueType526×577 23.1 KB

NonZero(Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember
)
AND ([Measures].[Issue type] = “Incidencia”
OR [Measures].[Issue type] = “Consulta”
OR [Measures].[Issue type] = “Bug”)
AND
DateCompare(
[Measures].[Issue resolution date],
‘2022-01-01’
)>0
),
[Measures].[Total resolution workdays]

Sum(
{[Transition Status].[Esperando por el cliente]},
([Measures].[workdays in transition status],
[Time].CurrentHierarchy.DefaultMember)
)

Sum(
{[Transition Status].[Pendiente de instalación]},
([Measures].[workdays in transition status],
[Time].CurrentHierarchy.DefaultMember)
)
))

2

Hi @JTejada
Try this formula, which should “listen” to Issue type page filters without manual coding of issue types.

NonZero(Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.get('Resolved at'),
[Time].CurrentHierarchyMember
)
AND
DateCompare(
[Measures].[Issue resolution date],
'2022-01-01'
)>0
),

CASE WHEN
[Measures].[Issues resolved]>0
THEN
[Measures].[Total resolution workdays]
-
Sum(
{[Transition Status].[Esperando por el cliente]},
([Measures].[workdays in transition status],
[Time].CurrentHierarchy.DefaultMember)
)
Sum(
{[Transition Status].[Pendiente de instalación]},
([Measures].[workdays in transition status],
[Time].CurrentHierarchy.DefaultMember)
)
END

))

Martins / eazyBI

3

Thank you very much Martins, it works !

I have seen that the difference is this:

CASE WHEN
[Measures].[Issues resolved]>0
THEN

Can you tell me the reason why adding that code, and filters correctly?

4

@JTejada

That line connects the calculation to page filter selection correctly.
Without it, the Transition Status dimension would check also historical issue types which might be different than selected ones.

Martins / eazyBI

1 Like
5

Perfect Martins, thank you very much !