Create an Epic Link Exclude link by Key

Questions & Answers
1

I have a report that has a link of Epic to include as one part of the report but now I want to exclude a link of Epic Link from a second column un the report

This is what I have for the Include

Aggregate(
{
[Epic Link].[Epic].getmemberbykey(“PSDVHD-706”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1078”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1200”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1231”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1233”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1132”)
}
)

but the one for Exclude is not working as expected

Aggregate(
Except(
[Epic Link].Members,
{
[Epic Link].[Epic].getmemberbykey(“PSDVHD-706”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1078”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1200”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1231”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1233”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1132”)
}
))

2

You are addressing [Epic Link].Members, this addresses any Epic Link member at any level, including, any project level member, any epic level member. This could lead to duplicated results.

You would like to address Epic level members only in Epic Link dimension and then for all Epic set you can define a set of Epics you want to exclude:

Aggregate(
  Except(
  [Epic Link].[Epic].Members,
  {
[Epic Link].[Epic].getmemberbykey(“PSDVHD-706”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1078”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1200”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1231”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1233”),
[Epic Link].[Epic].getmemberbykey(“PSDVHD-1132”)
}
))

You can also except members of another calculated member (I will use a calculated member name Test) as well:

Aggregate(
  Except(
  [Epic Link].[Epic].Members,
  ChildrenSet([Epic Link].[Test])
))

With this approach. You can define a calculated member, listing your epics. Then address this calculated member as a parameter (a set) for Except function. You would like to address members of this calculated member with ChildrenSet function. See an example formula above.

Daina / support@eazybi.com