cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xmark Helper I
Helper I

DAX query for flattened data from JIRA

hi all,

 

I am trying to extract data from my JIRA instance (via CSV) and build a report on points "completed" and "committed" per sprint.

 

When the CSV file exports, each Story has some flattened columns, Sprint, Sprint_12, Sprint_13 which appear to hold a value on which sprint each story started on (the "Sprint" column), and if the story moved over to the next sprint, then the value in Sprint_12 would contain the name of the next sprint and so on.

 

For example, Story ABC123 starts in "Sprint 1", but takes longer than a sprint to complete, so it moves into the next sprint, so the values for the Sprint column would be "Sprint 1" and Sprint_12 would be "Sprint 2".

 

If this story was worth 8 points, and was completed in Sprint 2. I want a report that shows a clustered column chart, showing 8 points "committed" in sprint 1, but zero points completed, and 8 points "committed" in Sprint 2, with 8 points completed.

 

I dont know how to construct the clustered column chart so that "some" axis field is used as the Sprint field (which one?) and then I want 2 measures for completed points for that sprint, and committed points for that sprint... 

 

It sounds easy, so I hope I am missing something small, but I can only get the "completed" numbers correct, but I dont know how to account for a story that was committed over 2 sprints.

2 REPLIES 2
Microsoft
Microsoft

Re: DAX query for flattened data from JIRA

Hi @xmark ,

 

Please eloborate your scenario with dummy data and desired output.

How to Get Your Question Answered Quickly

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
xmark Helper I
Helper I

Re: DAX query for flattened data from JIRA

Hi @v-yulgu-msft thanks ill try again to show some sample data and desired output.

 

Its only 1 table -called "JIRA" - and the relevant columns are:

StoryNumber, Sprint,Sprint_12,Sprint_13,Status, Story Points

StoryNumber is the unique key of the user story

Sprint is always the text field name of the sprint where the story started

IF Sprint_12 is populated, that means that the story started in the "Sprint" sprint, but was shifted into Sprint_12

IF Sprint_13 is populated, that means that the story started in the "Sprint" sprint, but was shifted into Sprint_12, THEN moved into Sprint_13

Status is the story status - I am only interested in "Done" stories

Story Points - is the allocated points that I want to total about the stories (think agile story points)

 

Here is some sample data:

StoryNumberSprintSprint_12Sprint_13StatusStory Points
SN123First Sprint  Done4
SN222First SprintSecond Sprint Done8
SN555Second Sprint  Done2
SN678Second SprintThird Sprint Done16
SN765Third SprintFourth Sprint Done4
SN888Third SprintFourth SprintFifth SprintDone8

 

My desired output would be a report per sprint (5 in the case above) - that really has 2 measures:

  1. Committed - This is the SUM of the Story Points of stories that were STARTED in each sprint
  2. Completed - This is the SUM of the Story Points of stories that were Done in each sprint
SPRINTCommittedCompleted
First Sprint124
Second Sprint1810
Third Sprint1220
Fourth Sprint00
Fifth Sprint08

 

I hope this makes sense!

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors