How To sort by 2 Columns, One ASC and the other DESC

I am looking to create a report that is sorted first alphabetically by sprint name (Sprint Name ASC) and then descending by Sprint End Date (End Date DESC).

I am able to create a calculated member that concatenates the two like this:

[Sprint].CurrentMember.Name || " " || Format( [Sprint].CurrentMember.get(‘Start date’), ‘yyyymmdd’)

but when I used that and sort ascending it is correct for sprint name but end date is not descending.

If I sort it by descending, it is correct for end date but sprint name is now descending.

What I’d like to learn how to do is:

[Sprint].CurrentMember.Name, ASC
then
Format( [Sprint].CurrentMember.get(‘Start date’), ‘yyyymmdd’), DESC

David

Does anyone have any ideas for this?

Hi,

Usually two parameters are used for sorting in the case when one of the parameters is the same for multiple elements.
Otherwise, it would always take the first parameter (Sprint name) to sort members.

Do you have the same sprint name for multiple sprints?
Perhaps you can share some examples of sprint names and their end dates that you would like to sort when creating a calculated member.

Martins / eazyBI team

I am trying to sort Ascending on Sprint Name (A-Z) and then Descending on Sprint End Date (newest first). This should put all the sprints of the same team together, and in descending chronological order.

Here is an example of the data.

SprintSortSample

Hi @Speedydgon

Please share more details on how do you define a team?
Can we understand from your screenshot how identify the team name (which is common for some of these sprints)?
Is that perhaps part of sprint name?

Martins / eazyBI

The first part of the Sprint name are the teams: 3CM, C360, SSO, UBI Mobile etc.

To explain better what I am trying to accomplish, I want to sort by 1 column (Sprint) which will put all the sprints for the same team together, then by Sprint End date which puts that teams’ sprint in chronological order.

Hi,

That could be challenging in your case as there is no clear pattern in sprint name to extract the team from, according to your screenshot some sprint names have dash between team and sprint number, some other don’t.
In order to make it work, there should be clear and strict rules how to identify the team from sprint names (rules that you could use in formulas).

When you have clear rules how to extract team from sprint names, you could create calculated measure that sorts sprints first by team (substring of sprint name) and then by sprint end dates.

Perhaps you could add new custom field (single-select) in Jira that stores the team and add this custom field to issue screen. Or align the sprint names (so all sprints have dash between team and sprint numbers)

Martins / eazyBI support

I think I can make it work without having team by just using just Sprint Name. Can you show me how I would create a calculated measure that sorts sprints first by sprint name and then by sprint end dates?

Hi,

Try this formula when creating a new calculated measure:

    Cast([Sprint].CurrentMember.Name as string)||" - "||
Cast(
Rank(
[Sprint].CurrentMember,
Order(
[Sprint].[Sprint].Members,
[Measures].[Sprint end date],
BASC
)
)as string)

Then sort your report by this new calculated measure in descending order.
Finally, you could click on the column and remove it. The sorting must remain in the report

Martins / eazyBI team

2 Likes

When I use [Measures].[Sprint End Date} I get mmm dd yyyy displayed but if I use [Sprint].CurrentMember.get(‘Complete date’) it works as expected. Why is that ?

This helps and I can make it work. Thank you.

David

Hi,

This output is likely because eazyBI can’t detect the default format for the calculated measure automatically.
Try choosing the “integer” format for your calculated measure manually.

Martins / eazyBI

That was it. Hadn’t really noticed that Formatting drop-down. I learned so much through this.

Thank you so very much Martin

1 Like

It works for me also.
Great solution.
Thanks.