nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

EazyBi Filter Calculated Member Beginner Question

Morris Coyle

Hi,

I should start off by saying I am new to EazyBi and have no knowledge of MDX at all.

I often create reports for our department and use the "search and bookmark" feature to filter out members of a Dimension called Primary Resolver Team. It dawned on me as I try and learn more than I could probably set these up as a filter using calculated members. Try as I might using aggregate and filter I cannot get the correct syntax to work.

For example i have teams called ABC-OCN-UNIX, ABC-OCN-WINDOWS, ABC-OCN-DBA etc. and what I need is one calculated member that houses them all.

I hope I have managed to explain that correctly :-)

Accepted answers

Roberts Čāčus - eazyBI

Hi @Morris Coyle ,

You are right; you can try to create a calculated member in the Primary Resolver Team dimension with the Aggregate() function. If the number of teams is reasonable, you can aggregate all the specific members. Please have a look at the formula below:

Aggregate({
  [Primary Resolver Team].[ABC-OCN-UNIX],
  [Primary Resolver Team].[ABC-OCN-WINDOWS],
  [Primary Resolver Team].[ABC-OCN-DBA],
  [Primary Resolver Team].[...]
})

Another option is to use the Filter() function. It looks like the teams have a common naming scheme. You can try to use that as a filtering condition if that is what you are looking for. Please have a look at the code below:

Aggregate(
  Filter([Primary Resolver Team].Members,
    [Primary Resolver Team].CurrentMember.Name MATCHES '^ABC-OCN-.*'
  )
)

You can read more about calculated members on the eazyBI documentation page - https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-members.

Kind regards,

Roberts // eazyBI support

All comments

Roberts Čāčus - eazyBI

Hi @Morris Coyle ,

Aggregate({
  [Primary Resolver Team].[ABC-OCN-UNIX],
  [Primary Resolver Team].[ABC-OCN-WINDOWS],
  [Primary Resolver Team].[ABC-OCN-DBA],
  [Primary Resolver Team].[...]
})

Aggregate(
  Filter([Primary Resolver Team].Members,
    [Primary Resolver Team].CurrentMember.Name MATCHES '^ABC-OCN-.*'
  )
)

You can read more about calculated members on the eazyBI documentation page - https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-members.

Kind regards,

Roberts // eazyBI support

Morris Coyle

Thank you! That worked perfectly.

Morris Coyle

Hi again,

Now i'm trying to not only filter on certain items (unfortunately unable to use the MATCHES function because they don't all follow an easy format) but I would like to exclude certain. I've been playing with AGGREGATE and EXCLUDE but quite get the formatting right.

Roberts Čāčus - eazyBI

Hi @Morris Coyle ,

You can try to use the Except() function inside the Aggregate() function, to exclude certain members from a dimension level. For example, if the Primary Resolver Team dimension has the "Primary Resolver Team" as the only level, you can create a calculation similar to the one below:

Aggregate(
  Except(
[Primary Resolver Team].[Primary Resolver Team].Members,
      {
[Primary Resolver Team].[ABC-OCN-UNIX],
[Primary Resolver Team].[ABC-OCN-WINDOWS],
[Primary Resolver Team].[ABC-OCN-DBA]
}
  )
)

The calculated member will then have all the members from that dimension, except the three defined in the Except() function.

Please have a look at the eazyBI documentation page to see some other examples - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members#Calculatedmembers-Aggregatemembersinotherdimensions.

Kind regards,

Roberts // eazyBI support

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

eazybi