Excluding a field value in reports

Hello Support

We have using eazyBi add in to add the widgets in the jira dashboards. to report only the original issues we need to exclude the issues with cancelled status and a multi level cascading fiend with value. (Level1 - Level 2) Ex: if the value L1/L2 categorization field values is Other - Duplicate the issues with this values are to be excluded from the EazyBI custom hierarch levels .
Thanks

Hi @AJAYGOUD19 ,
There are two options on how you can filter your data.
One is to create an aggregate member with except() in your dimensions. For the Status dimension it would be straightforward:

Aggregate(
  Except(
    [Status].[Status].Members,
    {[Status].[Cancelled]}
  )
)

For a multi-level cascading field, it is a bit trickier as the member would aggregate all the second levels as one using this formula:

Aggregate(
  Except(
  [DIMENSION NAME].[2n level].Members,
  {[DIMENSION NAME].[XXX].[Other - Duplicate],
  [DIMENSION NAME].[YYY].[Other - Duplicate]}
  )
)

Then use those dimensions with created members in the Pages to filter your report:

The second option is to create a calculated member that would count how many issues don’t have status Canceled and particular L1/L2 values.

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].CurrentHierarchy.Levels("Issue")),
    CoalesceEmpty([Measures].[Issue Category and Project], " ") NOT MATCHES ".* Other - Duplicate.*"
    AND
    [Measures].[Issue status] NOT MATCHES "Cancelled"
    AND
    [Measures].[Issues created]>0
  ),
  1
)

best,
Gerda // support@eazyBI.com