Filter from two measures

Questions & Answers
1

Hi guys,

I’m trying to filter one measure from another.
I want to see the “Workdays in transition status” but only in tickets with “Time to resolution - Breached” for a particular period.

I tried to use and play with the next calculated measure, but without luck:

CASE WHEN
  NOT IsEmpty([Measures].[Time to resolution - Breached (in period)])
THEN
	Sum(
	  --set of issues, filter issues created in the selected period
	  Filter(
		DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
		DateInPeriod(
		  [Issue].CurrentMember.Get('Created at'),
		  [Time].CurrentHierarchyMember)
	  ),
	  ([Measures].[Workdays in transition status], 
	  [Time].CurrentHierarchy.DefaultMember)
	)
END

Please help to figure out)

BR. Alex

2

Hi guys,

Any updates please?

3

Hi @AlexN

You are almost there!

I would suggest, first, filtering by resolution date: most probably, it is the date when SLA “Time to resolution” is completed (then, in the report, you will count time for this time period).
And, for the numerical expression, use a tuple you are already using, supplemented with
[Time to Resolution Breached].[Breached] to take workdays only for issues with breached this SLA.

I am not sure what is the measure as a condition in CASE WHEN part; while it is not needed to filter out breached issues, it might be a good optimization to check before further calculation whether there are any breached issues during this time period

CASE WHEN
  NOT IsEmpty([Measures].[Time to resolution - Breached (in period)])
THEN
	Sum(
	  --set of issues, filter issues created in the selected period
	  Filter(
		DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
		DateInPeriod(
		  [Issue].CurrentMember.Get('Resolved at'),
		  [Time].CurrentHierarchyMember)
	  ),
	  ([Measures].[Workdays in transition status], 
           [Time to Resolution Breached].[Breached],
	   [Time].CurrentHierarchy.DefaultMember)
	)
END

Best,
Ilze, support@eazybi.com

1 Like
4

Hi Ilze,

It’s working!
Thank you for the tip.

BR. Alex