Conditional calculated measures

Questions & Answers
,
1

I have created 2 calculated measures for tracking SLA Met and SLA Breached. Because we are tracking SLA’s differently for each Priority, we have to have different calculations for each priority level.

I currently have a measure that looks like this:

CASE [Priority].Currentmember.Name
WHEN “P0”
THEN
NonZero(SUM(
Except(
[age interval].[age interval].Members,
{
[age interval].[(none)],
[age interval].[04 - 05],
[age interval].[06 - 30],
[age interval].[31 - 90],
[age interval].[91 - …]
}
),
Aggregate (
{
[Issue Type].[Bug],
[Issue Type].[Escalation]
},
(
[Measures].[Open issues],
[Priority].[P0]
)
)))
WHEN “P1”
THEN
NonZero(SUM(
Except(
[age interval].[age interval].Members,
{
[age interval].[(none)],
[age interval].[06 - 30],
[age interval].[31 - 90],
[age interval].[91 - …]
}
),
Aggregate (
{
[Issue Type].[Bug],
[Issue Type].[Escalation]
},
(
[Measures].[Open issues],
[Priority].[P1]
)
)))
WHEN “P2”
THEN
NonZero(SUM(
Except(
[Age interval].[Age interval].Members,
{
[Age interval].[(none)],
[Age interval].[31 - 90],
[Age interval].[91 - …]
}
),
Aggregate (
{
[Issue Type].[Bug],
[Issue Type].[Escalation]
},
(
[Measures].[Open issues],
[Priority].[P2]
)
)))
WHEN “P3”
THEN
NonZero(SUM(
Except(
[Age interval].[Age interval].Members,
{
[Age interval].[(none)],
[Age interval].[91 - …]
}
),
Aggregate (
{
[Issue Type].[Bug],
[Issue Type].[Escalation]
},
(
[Measures].[Open issues],
[Priority].[P3]
)
)))
END

If I have all of my Priority hierarchy members selected, then it works fine like so:

image
image1382×965 138 KB

I have a third measure for all open issues selected just to check my math on Met + Breached = Total.

The issue I’m having, is that if I select “All Priorities” without expanding them, then I get no results on the report like so

image
image1448×727 98.7 KB

My “All Priority” member is a filtered list of our engineering priorities:

Aggregate({
[Priority].[P0],
[Priority].[P1],
[Priority].[P2],
[Priority].[P3]
})

Can someone help direct me to how I can get the “All Priority” column to show up under my “Met” and “Breached” measures?

2

Hi @jeff.tillett,
Sorry for the delayed answer.

To sum all values at your aggregate member “All Priority” level, you need to put your formula in this calculation:

Sum(
Generate(
    ChildrenSet([Priority].CurrentHierarchyMember),
    Descendants([Priority].CurrentHierarchyMember, [Priority].[Priority])),
-- paste your formula here
)

best regards,

Gerda // support@eazybi.com

3

That was it! For clarities sake in case anyone else finds this, the fix was to add Gerda’s code into my original conditional measure, with my original code living where she put “-- paste your formula here”

1 Like