EazyBI - How to filter to see a subset of a dimension with labels

David Lin August 8, 2016

I am trying to create a chart has labels as a dimension and limit the reported dimension with additional label(s). i.e. number of tickets that have both label1 AND label2 over time. 

I've tried to create a calculated member formula without success. I even tried to tweak the formula for this related question (https://answers.atlassian.com/questions/281425) and couldn't get the result I was looking for. Any thought?

Thank you. 

5 answers

1 vote
Daina Tupule eazyBI
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 14, 2016

Hi, David

You could try Aggregate function to create a calculated member in Labels dimension either by listing label members:

Aggregate({
  [Label].[Test1],
  [Label].[Test2]
  })

 or calculating them dynamically if they match some pattern:

Aggregate(
  Filter(
    [Label].[Label].Members,
    [Label].CurrentMember.Name MATCHES 'Test.*'
  ))

 

Daina / support@eazybi.com

David Lin August 17, 2016

Hi @Daina Tupule, Thank you for the reply.  For the first suggestion, it will show JIRA tickets that has either Test1 or Test2 labels. This is not what I am trying to accomplish. I am looking for tickets that have Test1 and Test2 labels. The second suggestion works fine for all labels that start with the same prefix. The labels that I plan to use does not share any prefix at all. i.e. Test1, MyRealTestLabel1, SomeOtherName, etc. I tried to nest two filters and it doesn't work for me.

 

Aggregate(
  Filter(Filter(
    [Label].[Label].Members,
    [Label].CurrentMember.Name MATCHES 'testLabel'),
    [Label].CurrentMember.Name MATCHES ‘second2'
 ))

I've also tried to use and, which also does not work. 

Aggregate(
Filter(
[Label].[Label].Members,
[Label].CurrentMember.Name MATCHES 'testLabel' and
[Label].CurrentMember.Name MATCHES 'second2'
))

The way I verified this is using this JQL: "labels = testLabel and labels = second2" I am able to see search results in JIRA with this query. The part I can't figure out is how to translate this information into a chart within EazyBI. 

Any recommendation?

Daina Tupule eazyBI
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 22, 2016

Hi David

I missed the part you would like to count issues with all listed labels.

You could use this calculated member (let's name it 'TEST labels') created in Labels dimension to create a new calculated measure in Measures dimension to count issues with all labels in this calculated member 'TEST labels'.

You could use this formula:

Count(Filter(
  Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
  ([Measures].[Issues created],
  [Label].[TEST labels]) = Count(ChildrenSet([Label].[TEST labels])))
)

This formula will count all issues that have all labels in 'TEST labels'. A tuple  'TEST labels' with measure 'Issues created' is used to retrieve label count from TEST labels for this issue and then it is compared to label count in calculated member 'TEST labels'. 

David Lin August 23, 2016

Hi @Daina Tupule, 

Thanks for the follow-up. I got out of memory error when tried your latest formula. Other charts seems to still load correctly. So, looks like this calculation is taking more memory. 

Failed to execute query. Error message:
OutOfMemory used=1305651120, max=1431830528

If I create a calculated measure in Measures dimension with the following, I can see a count of issues that has "TEST labels". However, when I tried to add Label column next to Measures to filter on a secondary label, say "A Second Test", I don't see issues being filtered in the chart. (i.e. number of issues didn't decrease)

 

([Measures].[Issues created],
  [Label].[TEST labels])
Eimantas Puscius July 25, 2018

Hello David, I was just wondering whether you were able to resolve the problem that you were having?

I am currently trying to group issues which have Label 1 AND Label 2, but have had no luck so far...

Many thanks 

0 votes
CH December 31, 2020

Hi All - 
I'm still searching, but I'm looking for a solution that is [Label1] and [Subset list of multiple labels] - it's two carveouts, everything in the subset that has Label1... Do I have to do that individually?

Trying to avoid
Label1
AND
subsetlabel1
OR
Label1
AND 
subsetlabel2

Does anyone have any thoughts or pointers?

Janis Plume _eazyBI_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 13, 2021

Hi,

 

The solution demonstrated by @Mitesh Verma is on the right track.

It can be advanced further with the following.

 

1) Create aggregated members for both subsets of labels.

2) Filter and count the set of issues matching the condition as provided earlier in this thread:

 

Count(Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
([Measures].[Issues created],
[Label].[Label1])>0
AND
([Measures].[Issues created],
[Label].[subsetlabel1]) = Count(ChildrenSet([Label].[subsetlabel1]))
OR
([Measures].[Issues created],
[Label].[Label1])>0
AND
([Measures].[Issues created],
[Label].[subsetlabel2]) = Count(ChildrenSet([Label].[subsetlabel1]))
))

Kindly,

Janis, eazyBI support

0 votes
Mitesh Verma August 26, 2020

Hello All.

After much trying , i found a solution that worked for me.  


Aggregate(Filter(
--iterate through issues
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),

([Measures].[Issues created],
[Label].[MyLabel1])>0
AND
([Measures].[Issues created],
[Label].[MyLabel2]) > 0

))

 

Let me know if this worked for you!

cheers

0 votes
Juliana Carlos November 12, 2019

Hello!

 

Im having having the same issue.. need to count the number of tickets with that label.

Anyone solved it?

Mitesh Verma August 26, 2020

Yes Juliana, i have added a comment below with calculated member details. :)

0 votes
Yogesh Mude[Jira]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 8, 2016

HI @David Lin

might be you are looking for result like as below Image.

 

eazyBI.png

 

David Lin August 8, 2016

Hi @Yogesh Mude[Jira], I am actually looking for a way to have one of the lines to represent count for multiple labels. For example, the last label is "Test" in the image you shared. In the scenario that I want to create, this "Test" line represents number of JIRA tickets that has two or more labels, for example "Test1" and "Test2". To do this, I figured that I need to create a calculated member formula and I couldn't figure out how to do so. 

Suggest an answer

Log in or Sign up to answer