Calculated member in Label dimension doesn't show sum of individual members

Questions & Answers
1

Hi all,

I’m trying to just get basic issue counts for a certain set of labels being used by a few teams in my org.

First, I created a calculated dimension that filters the labels I want:

[Label].[Team Label Counts] =
Aggregate(
    Filter(
      [Label].Members,
      [Label].CurrentMember.Name MATCHES 'team_name::.*'
    )
)

This correctly gives me Team Label Counts as an expandable row when I select it as the Label member for the report.

Next, I created a new measure to count a specific label according to another filter:

[Measures].[Approved] = 
Sum(Filter(
  [Measures].[Issues created],
  [Label].CurrentHierarchyMember.Name MATCHES '.*::Approved'
)
)

The end result I get is that I have a table where the row for the team is empty but, if I expand it, the individual rows corresponding to labels found with the filter in the Label calculated dimension show the values expected.

image

I was expecting the top row to just be the sum of the member rows below.

Any ideas why the calculated dimension is not aggregating the data from the individual elements? This method has worked fine before when I’ve used it with Fix Versions instead of Labels.

2

It seems to have something to do with the measure operating on the literal name of the calculated dimension rather than the members of that calculated dimension.

i.e., ‘Team Label Counts’ fails the MATCHES comparison with ‘.*::Approved’.

Now to figure out how to get it to operate on the members and sum them…

3

OK, I think I have it here but I have to admit I don’t completely understand why this approach gives the result I want and the more intuitive approach did not.

Basically, get the set of members of the label calculated dimension, remove the ones that don’t line up with what I’m after and sum those over the issues created measure.

Sum(
  Except(
    CascadingChildrenSet([Label].CurrentMember),
    Filter(
      [Label].Members,
      [Label].CurrentMember.Name NOT MATCHES '.*::Approved'
    )
  ),
  [Measures].[Issues created]
)
4

Hi @chrispy35,

I am happy you got the desired result on your own. If you are looking for just the one label “team_name::Approved” in the calculated measure “Approved”, you can define a simpler one with a construct called a tuple:

([Measures].[Issues created],
[Label].[team_name::Approved])

If there are several teams, and for each team, you have a set of calculated members in the Label dimension, I recommend using the calculated member(s) in the report. Then, the calculated measure “Approved” could look similar to the one below:

Sum(
  Filter(
    ChildrenSet([Label].CurrentMember),
    [Label].CurrentMember.Name MATCHES '.*::Approved'
  ),
  [Measures].[Issues created]
)

Best,
Roberts // support@eazybi.com

5

Hi Robert,

I tried the tuple method but that didn’t get the result I want due to (I think) the fact that the label dimension part of the tuple was a calculated member itself and had 3 individual labels within it.

Chris P.

6

…and I just tried your second suggestion as well which I was sure I’d tried but apparently not. That works great and is much cleaner and intuitive than what I came up with. I’ll mark that as the solution instead.

Thanks @roberts.cacus !

1 Like