Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
I have data with few columns for different teams.I want to display only last 5 sprints data in my bar chart displaying count of workitems vs sprint. When i try to use rank function its not working for me.I also tried at power query group rows but its giving wiered result.Each team is having IterationCycle with different number of sprints in it.
Sample data is attached herewith.
Work Item | Team | Iteration | IterationCycle | Sprint | Sprint.startDate | Sprint.Enddate |
-ASD
Work Item | Team | Iteration | IterationCycle | Sprint | Sprint.startDate | Sprint.Enddate |
201 | AS | PS/P 2022.1/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 11/3/2022 | 11/17/2022 |
202 | AS | PS/P 2022.1/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 11/3/2022 | 11/17/2022 |
203 | AS | PS/P 2022.1/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 10/20/2022 | 11/3/2022 |
204 | AS | PS/P 2022.1/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 10/20/2022 | 11/3/2022 |
205 | AS | PS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
206 | AS | PS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
207 | AS | PS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
208 | AS | PS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
209 | AS | PS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
210 | AS | PS/P 2022.1/Sprint 2022.1.6 | P 2022.1 | Sprint 2022.1.6 | 10/6/2022 | 10/20/2022 |
211 | AS | PS/P 2022.1/Sprint 2022.1.6 | P 2022.1 | Sprint 2022.1.6 | 10/6/2022 | 10/20/2022 |
212 | AS | PS/P 2022.1/Sprint 2022.1.5 | P 2022.1 | Sprint 2022.1.5 | 9/22/2022 | 10/6/2022 |
213 | AS | PS/P 2022.1/Sprint 2022.1.5 | P 2022.1 | Sprint 2022.1.5 | 9/22/2022 | 10/6/2022 |
214 | AS | PS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
289 | AS | PS/P 2022.1/Sprint 2022.1.1 | P 2022.1 | Sprint 2022.1.1 | 7/28/2022 | 8/11/2022 |
290 | AS | PS/P 2022.1/Sprint 2022.1.1 | P 2022.1 | Sprint 2022.1.1 | 7/28/2022 | 8/11/2022 |
291 | AS | PS/P 2022.1/Sprint 2022.1.1 | P 2022.1 | Sprint 2022.1.1 | 7/28/2022 | 8/11/2022 |
215 | AS | PS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
216 | SS | SS/P 2022.2/Sprint 2022.2.4 | P 2022.2 | Sprint 2022.2.4 | 11/3/2022 | 11/17/2022 |
217 | SS | SS/P 2022.2/Sprint 2022.2.4 | P 2022.2 | Sprint 2022.2.4 | 11/3/2022 | 11/17/2022 |
218 | SS | SS/P 2022.2/Sprint 2022.2.4 | P 2022.2 | Sprint 2022.2.4 | 11/3/2022 | 11/17/2022 |
219 | SS | SS/P 2022.2/Sprint 2022.2.3 | P 2022.2 | Sprint 2022.2.3 | 10/20/2022 | 11/3/2022 |
220 | SS | SS/P 2022.2/Sprint 2022.2.3 | P 2022.2 | Sprint 2022.2.3 | 10/20/2022 | 11/3/2022 |
221 | SS | SS/P 2022.2/Sprint 2022.2.3 | P 2022.2 | Sprint 2022.2.3 | 10/20/2022 | 11/3/2022 |
222 | SS | SS/P 2022.2/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 10/6/2022 | 10/20/2022 |
223 | SS | SS/P 2022.2/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 10/6/2022 | 10/20/2022 |
224 | SS | SS/P 2022.2/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 10/6/2022 | 10/20/2022 |
225 | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
226 | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
227 | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
228 | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
229 | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
230 | SS | SS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
231 | SS | SS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
232 | SS | SS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
233 | SS | SS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
234 | SS | SS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
235 | SS | SS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
236 | SS | SS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
237 | SS | SS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
238 | SS | SS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
239 | SS | SS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
240 | SS | SS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
241 | SS | SS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
Solved! Go to Solution.
Hi @ArundhatiD ,
Please update the formula of the measure [Rank] as below and check if that is what you want. You can find the details in the attachment.
Rank =
VAR _selprj =
SELECTEDVALUE ( 'Table'[Project] )
VAR _seldept =
SELECTEDVALUE ( 'Table'[Department] )
VAR _selteam =
SELECTEDVALUE ( 'Table'[Team] )
RETURN
RANKX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Project] = _selprj
&& 'Table'[Department] = _seldept
&& 'Table'[Team] = _selteam
),
CALCULATE ( MAX ( 'Table'[Sprint.startDate] ) ),
,
DESC,
DENSE
)
Best Regards
thank you
Hi @ArundhatiD ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a measure as below:
Rank =
VAR _selteam =
SELECTEDVALUE ( 'Table'[Team] )
RETURN
RANKX (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Team] = _selteam ),
CALCULATE ( MAX ( 'Table'[Sprint.startDate] ) ),
,
DESC,
DENSE
)
2. Create a bar chart and apply the visual-level filter with the condition(Rank is less than or equal to 5)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi Yingyinr,
Thank you very much ,Calculation is Working fine,
Can you suggest what changes i need to do in the calculation ,if i have Project and Department ,2 more levels (filters) along with Team. As I have 2 more levels in my data.
while ranking i have to consider these 2 levels along with Team.
The hierarchy is Project-----Department-------Team.The Actual columns are like .......
Consider
Work Item | Project | Department | Team | Iteration | IterationCycle | Sprint | Sprint.startDate | Sprint.Enddat |
Work Item | Project | Department | Team | Iteration | IterationCycle | Sprint | Sprint.startDate | Sprint.Enddate |
201 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 11/3/2022 | 11/17/2022 |
202 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 11/3/2022 | 11/17/2022 |
203 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 10/20/2022 | 11/3/2022 |
204 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 10/20/2022 | 11/3/2022 |
205 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
206 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
207 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
208 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
209 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
210 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.6 | P 2022.1 | Sprint 2022.1.6 | 10/6/2022 | 10/20/2022 |
211 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.6 | P 2022.1 | Sprint 2022.1.6 | 10/6/2022 | 10/20/2022 |
212 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.5 | P 2022.1 | Sprint 2022.1.5 | 9/22/2022 | 10/6/2022 |
213 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.5 | P 2022.1 | Sprint 2022.1.5 | 9/22/2022 | 10/6/2022 |
214 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
289 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.1 | P 2022.1 | Sprint 2022.1.1 | 7/28/2022 | 8/11/2022 |
290 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.1 | P 2022.1 | Sprint 2022.1.1 | 7/28/2022 | 8/11/2022 |
291 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.1 | P 2022.1 | Sprint 2022.1.1 | 7/28/2022 | 8/11/2022 |
215 | SRM | DEV | AS | AS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
216 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.4 | P 2022.2 | Sprint 2022.2.4 | 11/3/2022 | 11/17/2022 |
217 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.4 | P 2022.2 | Sprint 2022.2.4 | 11/3/2022 | 11/17/2022 |
218 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.4 | P 2022.2 | Sprint 2022.2.4 | 11/3/2022 | 11/17/2022 |
219 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.3 | P 2022.2 | Sprint 2022.2.3 | 10/20/2022 | 11/3/2022 |
220 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.3 | P 2022.2 | Sprint 2022.2.3 | 10/20/2022 | 11/3/2022 |
221 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.3 | P 2022.2 | Sprint 2022.2.3 | 10/20/2022 | 11/3/2022 |
222 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 10/6/2022 | 10/20/2022 |
223 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 10/6/2022 | 10/20/2022 |
224 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 10/6/2022 | 10/20/2022 |
225 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
226 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
227 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
228 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
229 | SRM | test | SS | SS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
230 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
231 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
232 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
233 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
234 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
235 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
236 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
237 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
238 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
239 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
240 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
241 | SRM | test | SS | SS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
242 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.4 | P 2022.2 | Sprint 2022.2.4 | 11/3/2022 | 11/17/2022 |
243 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.4 | P 2022.2 | Sprint 2022.2.4 | 11/3/2022 | 11/17/2022 |
244 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.4 | P 2022.2 | Sprint 2022.2.4 | 11/3/2022 | 11/17/2022 |
245 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.3 | P 2022.2 | Sprint 2022.2.3 | 10/20/2022 | 11/3/2022 |
246 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.3 | P 2022.2 | Sprint 2022.2.3 | 10/20/2022 | 11/3/2022 |
247 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.3 | P 2022.2 | Sprint 2022.2.3 | 10/20/2022 | 11/3/2022 |
248 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 10/6/2022 | 10/20/2022 |
249 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 10/6/2022 | 10/20/2022 |
250 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 10/6/2022 | 10/20/2022 |
251 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
252 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
253 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
254 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
255 | PQA | Arch | MS | MS/P 2022.2/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 9/22/2022 | 10/6/2022 |
256 | PQA | Arch | MS | MS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
257 | PQA | Arch | MS | MS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
258 | PQA | Arch | MS | MS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
259 | PQA | Arch | MS | MS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
260 | PQA | Arch | MS | MS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
261 | PQA | Arch | MS | MS/P 2022.1/Sprint 2022.1.4 | P 2022.1 | Sprint 2022.1.4 | 9/8/2022 | 9/22/2022 |
262 | PQA | Arch | PS | PS/P 2022.1/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 11/3/2022 | 11/17/2022 |
263 | PQA | Arch | PS | PS/P 2022.1/Sprint 2022.2.2 | P 2022.2 | Sprint 2022.2.2 | 11/3/2022 | 11/17/2022 |
264 | PQA | Arch | PS | PS/P 2022.1/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 10/20/2022 | 11/3/2022 |
265 | PQA | Arch | PS | PS/P 2022.1/Sprint 2022.2.1 | P 2022.2 | Sprint 2022.2.1 | 10/20/2022 | 11/3/2022 |
266 | PQA | Arch | PS | PS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
267 | PQA | Arch | PS | PS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
268 | PQA | Arch | PS | PS/P 2022.1/Sprint 2022.1.2 | P 2022.1 | Sprint 2022.1.2 | 8/11/2022 | 8/25/2022 |
269 | PQA | Arch | PS | PS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
270 | PQA | Arch | PS | PS/P 2022.1/Sprint 2022.1.3 | P 2022.1 | Sprint 2022.1.3 | 8/25/2022 | 9/8/2022 |
Your suggestion is too much needed.
-ASD
Hi @ArundhatiD ,
Please update the formula of the measure [Rank] as below and check if that is what you want. You can find the details in the attachment.
Rank =
VAR _selprj =
SELECTEDVALUE ( 'Table'[Project] )
VAR _seldept =
SELECTEDVALUE ( 'Table'[Department] )
VAR _selteam =
SELECTEDVALUE ( 'Table'[Team] )
RETURN
RANKX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Project] = _selprj
&& 'Table'[Department] = _seldept
&& 'Table'[Team] = _selteam
),
CALCULATE ( MAX ( 'Table'[Sprint.startDate] ) ),
,
DESC,
DENSE
)
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |