Drill down for dimension's calculation field

Questions & Answers
1

Hi,

I’ve been struggling to create a calculation member for a Dimension. Can you please help?

So we have a dimension Sprint and usually we used calc. member for each sprint. It is quite inconvenient, so we’re looking for a new solution. We want to have one calc. member for each year, and each time a new sprints arises, it will be automatically added.

Our sprints are starting with indication of the year (if year is 2022, then sprint’s code starts with 22.). Within one sprint we have sprints for each team.
What I am looking for is drill down like ‘year → sprint → all teams within the sprint’. Below I’ve created a calc member that includes all sprints for one year, however, I can’t make it drill down by each sprint.

The same logic is used for Time dimension and it works perfectly fine. I guess it’s because predefined attribute of Time in EazyBI/Jira data.

Aggregate({
  Filter(
    [Sprint].[Sprint].Members,
    [Sprint].CurrentMember.Name MATCHES "22.*"
  )
})

Apologies if the question was asked already or there is documentation for it, I haven’t found it yet.
Thank you in advance!

Best regards,
Alsu

2

Hi, @Alsu_Zaynutdinova

Where do you enter “Team” in Jira?
Is Team a single-select Jira custom field or perhaps by Team you mean Sprint boards?

Martins / eazyBI

3

Hi @martins.vanags ,

Yes you are right, sorry for confusing. If we select Sprint as a Row, then under “Select individual members” we have groups of sprints, where one calculation field contains sprint boards for different teams, but for one period.
So this ‘grouping’ works for the period of one sprint, and we can drill down for to individual sprint boards. But what we need is to be able to select one year, that will drill down to one period of sprint, and then to individual boards.
Is this possible to do? I’ve seen some of EazyBI’s replies with drill down code, but I could not implement anything like that for this task.

Thank you in advance!

4

@Alsu_Zaynutdinova

Check this demo report Active (multiple) sprints story points burn-down - Issues - Jira Demo - eazyBI

When you open the “Sprint” dimension you can see several examples of calculated members there.
Check “Last 5 closed sprints from all boards” which aggregates last 5 closed sprints from each board

Martins / eazyBI

5

Thank you @martins.vanags ,

This is almost the same code that we have now for sprint boards. What we need is something like below. We need to be able to create 2 levels of drill down for the Sprint dimension.
image

Sorry that this thread is too long now and thank you for your time.

Kind wishes,
Alsu

6

@Alsu_Zaynutdinova

Unfortunately, it won’t be possible to create a multi-level hierarchy in the “Sprint” dimension as in your example.
You could try creating a calculated member that is an aggregate of other calculated members. For example. you create a calculated member that filters all boards for one team for 23.1, then another calculated member with Aggregate of ChildrenSets from all these previously created calculated mebmers.

All boards for 23.1:

Aggregate(
Filter(
[Sprint].[Board].Members,
--condition for filter
)
)

All sprints in 2023:

Aggregate({
Childrenset([Sprint].[All boards for 23.1]),
Childrenset([Sprint].[All boards for 23.2])
})

Then use just the last member which can be expanded to two levels.

Martins / eazyBI

1 Like
7

Even with additional data import and extra sprint properties, it’s not possible to create additional level hierarchies for the Sprint dimension? I have a similar need, where I need to see for instance “quarter 1” then under that “Sprint Cycle 1” then under that each sprint in that aggregate.

8

Thank you @martins.vanags for confirming it.