EazyBI - How to filter so I only see a subset of a dimension

[Doug Swartz]

I'm just getting started with EazyBI. I've created a report which uses labels as a Dimension. This works fine. However, I want to limit the reported dimension to only include labels that start with the characters D1.

I assume I need to use a calculated member with the filter function to do this, but need a pointer on how to do so.

Thank you.

[Lauma Cīrule · Accepted solution]

Hi,

If you only want to filter out these label members (without showing totals), you can click on one of the Label names and add "Filter by name" matches: ^D1
This will leave only labels that start with D1. See documentation about using regular expressions (^ symbol to indicate that name has to start with "D1").

Another option is to create a group of labels that start with D1 in Label dimension calculated members. Formula for that would be

Aggregate({
  Generate(Filter(NonEmptyCrossJoin(
      Descendants([Label].CurrentMember, [Label].[Label]),
        [Measures].DefaultMember),
    [Label].CurrentMember.Name MATCHES '^D1.*'), 
  [Label].[Label].CurrentMember )
})

This will also return sum of measures for all labels that start with D1.

Kind regards,
Lauma

[Doug Swartz]

Perfect. MDX will soon be my friend.

[David Lin]

I would like to do something similar. Instead of filter label names that start with "D1", I would like to create a calculated member that filter by multiple labels. This means that I would like to get a single count of tickets that has specific multiple labels associated with the ticket. How do I define the formula? Thanks!

[Eimantas Puscius]

David, did you ever come to a solution regarding this?

[Shyam Suyambu]

This is great; thank you. I've been using this for a while now. In my latest report for a different purpose, I have run into a puzzling snag with this. I'm basically trying to aggregate certain transitions and summing the "days in transition status" measure. I'm trying to sum up all the transitions that end with "Test Ready". Here is what I have used:

Aggregate({
Generate(Filter(NonEmptyCrossJoin(
Descendants([Transition].CurrentMember, [Transition].[Transition]),
[Measures].DefaultMember),
trim([Transition].CurrentMember.Name) MATCHES '.*Test Ready$'),
[Transition].[Transition].CurrentMember )
})

My input "data set" is:

Open => Test Ready
In Progress => Test Ready
Reopened => Test Ready
Resolved => Test Ready
In Review => Test Ready
Refining => Test Ready
Integration Test Prep => Test Ready
Test Ready => Test Ready
Testing => Test Ready
Ready For Release => Test Ready
Ready => Test Ready
Committed => Test Ready

The MDX logic is picking up 9 of the above, except for these three:

Resolved => Test Ready
Refining => Test Ready
Ready For Release => Test Ready

Why would the pattern match everything else but these three? I've almost gone insane trying to figure out what's wrong. And I am sure it's something pretty silly that I have missed. Can someone help please?

[Lauma Cīrule]

Hi @Shyam Suyambu!

In this case you actually should not use the [Measures].DefaultMember as the default measure is Issues created and it most likely is empty for the Transition. Please try the following simplified MDX

Aggregate({
  Generate(Filter(
    Descendants([Transition].CurrentMember, [Transition].[Transition]),
    trim([Transition].CurrentMember.Name) MATCHES '.*Test Ready$'),
  [Transition].[Transition].CurrentMember )
})

Lauma / support@eazybi.com

[Shyam Suyambu]

@Lauma Cīrule Worked like a charm! Added "Transition" to the list of columns and defined a new calculated member with the above MDX. Thank you Lauma - you saved me and our teams countless hours of probably worthless research.

[Sitaram Chirravuri]

Newbie to eazyBI, trying to filter column Actual_PA, don't display if there is no value and I want the summary rows as well, if put "." on the column filer, it is removing all summary rows.

[Sir Mārtiņš Vanags]

Hi,

Could you please share the report definition?

https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Exportandimportreportdefinitions

And could you somehow ilustrate this behavior of your report when you enable the filter.

Perhaps, you could reach out to support@eazybi.com

Br,