Linking external issues to Jira sprints

Questions & Answers
1

I imported external data via data import. Is there a way to connect them to other data in the same cube via date?

What I have in my cube:

  • Issues with a resolution date (not from Jira)
  • List of my Jira Sprints (containing name, start and end date)

What I want:

  • Link these issues to Jira Sprint via the resolution date.

What I tried:

[Measures].[Tickets Resolved Count] is calculated while importing the data with the Date count measure option.

With Sprints as rows:

First try:

NonZero(SUM(
Filter(
Descendants([JiraSprint].CurrentMember, [JiraSprint].[JiraSprint]),
DateInPeriod(
[JiraSprint].CurrentMember.GetDate(‘Sprint Start’),
[Time].CurrentHierarchyMember)),
[Measures].[Tickets Resolved Count]
))

Second try:

NonZero(Sum(
Filter(
Descendants([Tickets].CurrentMember,[Tickets].[Ticket]),
DateBetween(
[Tickets].CurrentMember.GetDate(‘Resolution Date’),
[JiraSprint].CurrentHierarchyMember.GetDate(‘Sprint Start’),
[JiraSprint].CurrentHierarchyMember.GetDate(‘Sprint End’))
),
[Measures].[Tickets Resolved Count]
))

Third try:

With Tickets as rows:

CASE WHEN
DateBetween([Tickets].CurrentHierarchyMember.GetDate(‘Resolution Date’),
[Measures].[JiraSprint Sprint Start],
[Measures].[JiraSprint Sprint End]
)
THEN [Measures].[Tickets Resolved Count]
END

Is there a way to do this within eazyBI without adding this information to my (very long) csv file with Excel… :scream:

2

Hi @sven

Thanks for the elaborate description!

It seems that this code should do the trick if you have the individual Tickets in Rows:

Filter(
  Descendants([JiraSprint].CurrentMember,[JiraSprint].[JiraSprint]),
  DateBetween(
    [Tickets].CurrentHierarchyMember.GetDate('Resolution Date'),
    [JiraSprint].CurrentHierarchyMember.GetDate('Sprint Start'),
    [JiraSprint].CurrentHierarchyMember.GetDate('Sprint End')
  )
).Item(0).Name

This will, for every Ticket, iterate through all Sprints and find which Sprint dates fit with the Resolution date of the Ticket. This formula assumes that there will be only one sprint after the filtering and it will retrieve this Item and return the Name of this Sprint.

Let me know if this works as expected!
Best regards,
Nauris / eazyBI support

1 Like
3

Hey @nauris.malitis,

good to hear from you again! It works, thank you!

I have one weird thing now: If the filter doesn’t find a valid sprint, it returns #null instead of empty cell, so the CoalesceEmpty function doesn’t work. IsEmpty doesn’t work either. Do I have to test with IIF or is there another trick to use with the filter?

Best
Sven

4

I guess I will be talking to myself again… :rofl:

The result of the filter is a set, that was where I went wrong. I am checking now with .Count if there is a result in which case I show the item’s name.

All good. :smiley:

And thanks again for your great help!

5

Hi @sven

Yep, you got it right! The Descendants function returns a set, and the Filter function returns a filtered set.

Count() or .Count will both work in this case to check if the set has any values.

Great to hear from you too and be free to use Community for productive solution-finding even if you’re having discussions with yourself :grinning: :100:

Cheers!
Nauris

1 Like