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 :-)

[Roberts Čāčus · Accepted solution]

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

[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]

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