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

[David Lin]

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. 

[Daina Tupule eazyBI]

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]

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]

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]

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]

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 

[CH]

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

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

[Mitesh Verma]

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

[Juliano Carlos]

Hello!

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

Anyone solved it?

[Mitesh Verma]

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

[Yogesh Mude[Jira]]

HI @David Lin

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

eazyBI.png

[David Lin]

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.