Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Column value gets dulicated - how to only show the first value?

Hi all,

 

I have issue with a specific topic which I need som help to create a measure to.

 

So I need to create a measure that visualizes how many issues I have taken into my current work sprint.

 

The problem is, that if the issue is not solved within the work sprint, the issue will "jump" to the next work sprint.

 

This causes the problem that I cannot use the data to estimate how "good" we are at taking issues into our work sprint because the data accummulates from previous work sprints.

 

I need a visual that shows me how many issues I have taken into my work sprint without showing the issues that have "jumped" from work sprint to work sprint.

 

This will be used in order to help our team to better estimate how many issues we should commit to for each work sprint.

 

vv.JPG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

SOLUTION:

 

Finally I found a solution.

 

By using the following to find out:

 

In which sprint did the issue start = CALCULATE(MIN('Jira (base)'[Sprint No]);FILTER('Jira (base)';'Jira (base)'[Project.key.ID]=EARLIER('Jira (base)'[Project.key.ID])))
 
and:
 
In which sprint did the issue end = CALCULATE(MAX('Jira (base)'[Sprint No]);FILTER('Jira (base)';'Jira (base)'[Project.key.ID]=EARLIER('Jira (base)'[Project.key.ID])))

View solution in original post

13 REPLIES 13
PaulDBrown
Community Champion
Community Champion

@Anonymous 

 

I think I may have come up with a way (if @mwegener  suggestion does not work), albeit rather convoluted (there must be an easier way...)
Firstly I ordered the table in Power Query in ascending order by the Sprint field. I then added and index column:

Index column.JPG

Then these measures:

1) calculate the minimum index per project in the table:

 

Min Index per project = CALCULATE(MIN('DataTable'[Index]); ALLEXCEPT('DataTable'; 'DataTable'[Project Key]))

 

2) Compare this value to the original index. If it is the same, compute 1

 

equals index? = IF(MIN('DataTable'[Index]) = [Min Index per project]; 1; BLANK())

 

3) And finally sum the values returned by [equals index?]:

 

Net Number of projects = SUMX(
                SUMMARIZE('DataTable'; 'DataTable'[Sprint]; 'DataTable'[Project Key]; 
                "equals?"; [equals index?]); [equals?])

 

 

Which delivers this:

results.JPG

 

Which I think is what you are looking for...

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

@PaulDBrown My "net number of project" only gives me =1 back in all fields.

 

 

What have I done wrong?

@Anonymous
1) can you check the measures by setting up the left table in my example and post a screenshot?
2) can you post the measures you are using?




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @Anonymous ,

 

can you group the data by Project.key.ID, Date.created and return the MAX Sprint?

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Hi @mwegener  - I can group them - but what do you mean by "return the MAX sprint"?

Hi @Anonymous ,

 

I thought you always wanted to see the highest (MAX) sprint number.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

@mwegener- Yes you are right! 

 

I want to see in which sprint the project.key.id (issue) has started and how many times it has "jumped" to next sprints.

Hi @Anonymous ,

 

can you provide some sample data (as text)?

FYI: https://www.goodly.co.in/copy-paste-data-power-bi/

CopyPaste.png

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

@mwegener 

 

Sprint,Project.key.ID,Date.created
BIFI Sprint 20,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 31,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 32,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 33,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 34,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 35,BIBSO-10,2019-02-13T10:02:13.000+0000
Sprint 35,BIBSO-100,2019-11-20T09:39:14.000+0000
Sprint 36,BIBSO-100,2019-11-20T09:39:14.000+0000
Sprint 37,BIBSO-100,2019-11-20T09:39:14.000+0000
Sprint 38,BIBSO-100,2019-11-20T09:39:14.000+0000
Sprint 35,BIBSO-101,2019-11-05T09:54:51.000+0000
Sprint 35,BIBSO-105,2019-12-02T14:11:43.000+0000
Sprint 36,BIBSO-105,2019-12-02T14:11:43.000+0000
Sprint 34,BIBSO-107,2019-12-04T08:22:00.000+0000
Sprint 35,BIBSO-107,2019-12-04T08:22:00.000+0000
Sprint 35,BIBSO-118,2019-12-13T06:56:05.000+0000
Sprint 35,BIBSO-119,2019-12-12T13:19:16.000+0000
Sprint 35,BIBSO-12,2019-08-30T11:00:31.000+0000
Sprint 35,BIBSO-120,2019-12-12T13:20:15.000+0000
Sprint 36,BIBSO-125,2019-12-20T07:51:23.000+0000
Sprint 35,BIBSO-126,2019-12-20T11:00:26.000+0000
Sprint 35,BIBSO-128,2019-12-27T05:46:45.000+0000
Sprint 35,BIBSO-129,2019-12-27T05:47:33.000+0000
Sprint 35,BIBSO-130,2019-12-27T05:47:39.000+0000
Sprint 35,BIBSO-131,2020-01-02T13:38:17.000+0000
Sprint 35,BIBSO-132,2020-01-06T11:26:55.000+0000
Sprint 36,BIBSO-132,2020-01-06T11:26:55.000+0000
Sprint 37,BIBSO-132,2020-01-06T11:26:55.000+0000
Sprint 38,BIBSO-132,2020-01-06T11:26:55.000+0000
Sprint 36,BIBSO-133,2020-01-06T13:17:07.000+0000
Sprint 37,BIBSO-134,2020-01-08T09:01:26.000+0000
Sprint 37,BIBSO-135,2020-01-08T09:03:10.000+0000
Sprint 37,BIBSO-136,2020-01-08T09:04:12.000+0000
Sprint 37,BIBSO-137,2020-01-08T09:04:46.000+0000
Sprint 36,BIBSO-142,2020-01-09T13:11:29.000+0000
Sprint 36,BIBSO-143,2020-01-09T13:13:45.000+0000
Sprint 35,BIBSO-146,2020-01-13T11:09:41.000+0000
Sprint 35,BIBSO-147,2020-01-13T11:11:58.000+0000
Sprint 35,BIBSO-148,2020-01-13T11:14:06.000+0000
Sprint 35,BIBSO-149,2020-01-13T11:14:52.000+0000
Sprint 35,BIBSO-150,2020-01-13T11:15:18.000+0000
Sprint 35,BIBSO-151,2020-01-13T11:16:20.000+0000
Sprint 36,BIBSO-152,2020-01-14T07:42:39.000+0000
Sprint 36,BIBSO-153,2020-01-14T07:43:29.000+0000
Sprint 37,BIBSO-153,2020-01-14T07:43:29.000+0000
Sprint 36,BIBSO-154,2020-01-14T08:18:27.000+0000
Sprint 36,BIBSO-156,2020-01-14T08:48:15.000+0000
Sprint 35,BIBSO-158,2020-01-15T05:38:16.000+0000
Sprint 36,BIBSO-158,2020-01-15T05:38:16.000+0000
Sprint 37,BIBSO-158,2020-01-15T05:38:16.000+0000
Sprint 38,BIBSO-158,2020-01-15T05:38:16.000+0000
Sprint 35,BIBSO-159,2020-01-15T05:39:16.000+0000
Sprint 36,BIBSO-159,2020-01-15T05:39:16.000+0000
Sprint 37,BIBSO-159,2020-01-15T05:39:16.000+0000
Sprint 38,BIBSO-159,2020-01-15T05:39:16.000+0000
Sprint 35,BIBSO-160,2020-01-15T05:39:25.000+0000
Sprint 36,BIBSO-160,2020-01-15T05:39:25.000+0000
Sprint 37,BIBSO-160,2020-01-15T05:39:25.000+0000
Sprint 38,BIBSO-160,2020-01-15T05:39:25.000+0000
Sprint 35,BIBSO-161,2020-01-15T05:39:51.000+0000
Sprint 36,BIBSO-161,2020-01-15T05:39:51.000+0000
Sprint 37,BIBSO-161,2020-01-15T05:39:51.000+0000
Sprint 38,BIBSO-161,2020-01-15T05:39:51.000+0000
Sprint 35,BIBSO-162,2020-01-15T05:40:01.000+0000
Sprint 36,BIBSO-162,2020-01-15T05:40:01.000+0000
Sprint 37,BIBSO-162,2020-01-15T05:40:01.000+0000
Sprint 38,BIBSO-162,2020-01-15T05:40:01.000+0000
Sprint 35,BIBSO-163,2020-01-15T05:40:10.000+0000
Sprint 36,BIBSO-163,2020-01-15T05:40:10.000+0000
Sprint 37,BIBSO-163,2020-01-15T05:40:10.000+0000
Sprint 38,BIBSO-163,2020-01-15T05:40:10.000+0000
Sprint 35,BIBSO-164,2020-01-15T05:40:24.000+0000
Sprint 36,BIBSO-164,2020-01-15T05:40:24.000+0000
Sprint 37,BIBSO-164,2020-01-15T05:40:24.000+0000
Sprint 38,BIBSO-164,2020-01-15T05:40:24.000+0000
Sprint 36,BIBSO-165,2020-01-17T09:50:30.000+0000
Sprint 37,BIBSO-165,2020-01-17T09:50:30.000+0000
Sprint 38,BIBSO-165,2020-01-17T09:50:30.000+0000
Sprint 36,BIBSO-166,2020-01-21T06:31:21.000+0000
Sprint 36,BIBSO-167,2020-01-21T06:51:05.000+0000
Sprint 36,BIBSO-168,2020-01-21T06:51:06.000+0000
Sprint 36,BIBSO-169,2020-01-21T06:54:14.000+0000
Sprint 36,BIBSO-170,2020-01-21T06:54:31.000+0000
Sprint 36,BIBSO-171,2020-01-21T06:55:09.000+0000
Sprint 36,BIBSO-172,2020-01-21T06:55:16.000+0000
Sprint 36,BIBSO-173,2020-01-21T06:55:27.000+0000
Sprint 36,BIBSO-174,2020-01-22T04:27:29.000+0000
Sprint 37,BIBSO-174,2020-01-22T04:27:29.000+0000
Sprint 36,BIBSO-175,2020-01-13T11:18:43.000+0000
Sprint 37,BIBSO-175,2020-01-13T11:18:43.000+0000
Sprint 36,BIBSO-176,2020-01-27T04:31:18.000+0000
Sprint 37,BIBSO-176,2020-01-27T04:31:18.000+0000
Sprint 36,BIBSO-179,2020-01-28T08:26:11.000+0000
Sprint 37,BIBSO-179,2020-01-28T08:26:11.000+0000
Sprint 36,BIBSO-180,2020-01-28T12:08:30.000+0000
Sprint 37,BIBSO-180,2020-01-28T12:08:30.000+0000
Sprint 37,BIBSO-191,2020-01-30T14:47:36.000+0000
Sprint 36,BIBSO-192,2020-01-31T07:02:06.000+0000
Sprint 37,BIBSO-192,2020-01-31T07:02:06.000+0000
Sprint 36,BIBSO-198,2020-01-31T07:22:41.000+0000
Sprint 37,BIBSO-198,2020-01-31T07:22:41.000+0000
Sprint 36,BIBSO-199,2020-01-31T07:23:35.000+0000
Sprint 37,BIBSO-199,2020-01-31T07:23:35.000+0000
Sprint 36,BIBSO-200,2020-01-31T07:23:50.000+0000
Sprint 37,BIBSO-200,2020-01-31T07:23:50.000+0000
Sprint 36,BIBSO-201,2020-01-31T08:14:27.000+0000
Sprint 37,BIBSO-201,2020-01-31T08:14:27.000+0000
Sprint 36,BIBSO-202,2020-01-31T08:14:42.000+0000
Sprint 37,BIBSO-202,2020-01-31T08:14:42.000+0000
Sprint 37,BIBSO-204,2020-02-03T09:38:51.000+0000
Sprint 37,BIBSO-205,2020-02-03T09:39:34.000+0000
Sprint 37,BIBSO-206,2020-02-03T09:39:49.000+0000
Sprint 37,BIBSO-207,2020-02-03T09:40:02.000+0000
Sprint 37,BIBSO-208,2020-02-04T06:49:36.000+0000
Sprint 37,BIBSO-209,2020-02-04T06:51:35.000+0000
Sprint 37,BIBSO-210,2020-02-04T06:51:56.000+0000
Sprint 37,BIBSO-211,2020-02-04T06:52:28.000+0000
Sprint 37,BIBSO-212,2020-02-04T06:52:45.000+0000
Sprint 37,BIBSO-213,2020-02-04T11:05:36.000+0000
Sprint 37,BIBSO-214,2020-02-04T11:21:10.000+0000
Sprint 38,BIBSO-214,2020-02-04T11:21:10.000+0000
Sprint 37,BIBSO-216,2020-02-10T09:13:33.000+0000
Sprint 37,BIBSO-217,2020-02-10T13:17:59.000+0000
Sprint 37,BIBSO-218,2020-02-11T07:41:32.000+0000
Sprint 35,BIBSO-3,2019-07-04T12:31:44.000+0000
Sprint 30,BIBSO-54,2019-09-12T11:12:59.000+0000
Sprint 32,BIBSO-54,2019-09-12T11:12:59.000+0000
Sprint 33,BIBSO-54,2019-09-12T11:12:59.000+0000
Sprint 34,BIBSO-54,2019-09-12T11:12:59.000+0000
Sprint 35,BIBSO-54,2019-09-12T11:12:59.000+0000
BIFI Sprint 20,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 31,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 32,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 33,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 34,BIBSO-68,2019-11-04T07:07:09.000+0000
Sprint 35,BIBSO-68,2019-11-04T07:07:09.000+0000
BIFI Sprint 20,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 31,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 32,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 33,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 34,BIBSO-69,2019-11-04T07:07:24.000+0000
Sprint 35,BIBSO-69,2019-11-04T07:07:24.000+0000
BIFI Sprint 20,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 31,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 32,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 33,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 34,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 35,BIBSO-70,2019-11-04T07:08:18.000+0000
Sprint 33,BIEHSM-55,2019-11-21T12:03:19.000+0000
Sprint 34,BIEHSM-55,2019-11-21T12:03:19.000+0000
Sprint 35,BIEHSM-55,2019-11-21T12:03:19.000+0000
Sprint 35,BIEHSM-60,2019-12-16T08:58:48.000+0000
Sprint 36,BIEHSM-61,2020-01-09T13:06:46.000+0000
Sprint 37,BIEHSM-61,2020-01-09T13:06:46.000+0000
Sprint 36,BIEHSM-64,2020-01-24T08:06:40.000+0000
Sprint 37,BIEHSM-64,2020-01-24T08:06:40.000+0000
Sprint 36,BIEHSM-65,2020-01-24T08:07:27.000+0000
Sprint 37,BIEHSM-65,2020-01-24T08:07:27.000+0000
Sprint 36,BIEHSM-66,2020-01-24T08:08:07.000+0000
Sprint 37,BIEHSM-66,2020-01-24T08:08:07.000+0000
Sprint 36,BIEHSM-67,2020-01-24T08:08:33.000+0000
Sprint 37,BIEHSM-67,2020-01-24T08:08:33.000+0000
Sprint 36,BIEHSM-68,2020-01-24T08:08:53.000+0000
Sprint 37,BIEHSM-68,2020-01-24T08:08:53.000+0000
Sprint 36,BIEHSM-69,2020-01-24T08:09:07.000+0000
Sprint 37,BIEHSM-69,2020-01-24T08:09:07.000+0000
Sprint 36,BIEHSM-70,2020-01-24T08:09:23.000+0000
Sprint 37,BIEHSM-70,2020-01-24T08:09:23.000+0000
Sprint 37,BIEN-137,2020-01-31T07:10:11.000+0000
Sprint 37,BIEN-138,2020-01-31T07:10:43.000+0000
Sprint 37,BIEN-139,2020-02-05T13:01:52.000+0000
Sprint 37,BIEN-140,2020-02-05T13:02:04.000+0000
Sprint 37,BIEN-141,2020-02-05T13:02:16.000+0000
Sprint 26,BIHR-192,2019-08-07T12:22:30.000+0000
Sprint 27,BIHR-192,2019-08-07T12:22:30.000+0000
Sprint 37,BIHR-192,2019-08-07T12:22:30.000+0000
Sprint 26,BIHR-201,2019-08-14T08:43:02.000+0000
Sprint 27,BIHR-201,2019-08-14T08:43:02.000+0000
Sprint 37,BIHR-201,2019-08-14T08:43:02.000+0000
Sprint 26,BIHR-202,2019-08-14T08:43:59.000+0000
Sprint 27,BIHR-202,2019-08-14T08:43:59.000+0000
Sprint 37,BIHR-202,2019-08-14T08:43:59.000+0000
Sprint 26,BIHR-203,2019-08-14T08:44:36.000+0000
Sprint 27,BIHR-203,2019-08-14T08:44:36.000+0000
Sprint 37,BIHR-203,2019-08-14T08:44:36.000+0000
Sprint 26,BIHR-204,2019-08-15T10:14:37.000+0000
Sprint 27,BIHR-204,2019-08-15T10:14:37.000+0000
Sprint 37,BIHR-204,2019-08-15T10:14:37.000+0000
Sprint 35,BIHR-221,2019-11-12T12:23:55.000+0000
Sprint 35,BIHR-230,2019-12-18T08:23:19.000+0000
Sprint 36,BIHR-230,2019-12-18T08:23:19.000+0000
Sprint 26,BIHR-231,2020-02-07T08:12:20.000+0000
Sprint 27,BIHR-231,2020-02-07T08:12:20.000+0000
Sprint 37,BIHR-231,2020-02-07T08:12:20.000+0000
Sprint 30,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 31,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 32,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 33,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 34,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 35,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 36,BIIT-75,2019-08-28T12:20:58.000+0000
Sprint 36,BIIT-78,2020-01-09T08:20:35.000+0000
Sprint 37,BIIT-78,2020-01-09T08:20:35.000+0000
Sprint 38,BIIT-78,2020-01-09T08:20:35.000+0000
Sprint 30,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 31,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 32,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 33,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 34,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 35,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 36,BIIT-79,2020-01-16T11:19:08.000+0000
Sprint 30,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 31,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 32,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 33,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 34,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 35,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 36,BIIT-80,2020-01-16T11:20:32.000+0000
Sprint 37,BIIT-85,2020-02-06T10:23:05.000+0000
Sprint 37,BIIT-86,2020-02-06T10:24:29.000+0000
Sprint 37,BIIT-87,2020-02-06T10:25:08.000+0000

Hi @Anonymous ,

 

take a look at this.

PBIX

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

@mwegener- thank you for the pbit file.

 

i like the method how you made the solution.

 

could you please share the methods/calculations with me how you did it?

Hi @Anonymous,

 

I started with your data as basic table, extracted the sprint number and disabled the loading.

2020-02-24 21_39_58-Window.png

Then I create a reference to the query and group the data according to the Project.key.ID.
Again I disabled the loading.

2020-02-24 21_44_19-Window.png

Then I combined both queries using the Project.key.Id and (Max) Sprint No as a new query and reduced the number of rows (jumps) by one.

2020-02-24 21_45_21-Window.png

You can follow every step in detail in the PBIX file.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

SOLUTION:

 

Finally I found a solution.

 

By using the following to find out:

 

In which sprint did the issue start = CALCULATE(MIN('Jira (base)'[Sprint No]);FILTER('Jira (base)';'Jira (base)'[Project.key.ID]=EARLIER('Jira (base)'[Project.key.ID])))
 
and:
 
In which sprint did the issue end = CALCULATE(MAX('Jira (base)'[Sprint No]);FILTER('Jira (base)';'Jira (base)'[Project.key.ID]=EARLIER('Jira (base)'[Project.key.ID])))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.