Solved: Rank function is not working properly. Want to see...

ArundhatiD · ‎11-10-2022

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

v-yiruan-msft · ‎11-13-2022

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

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

View solution in original post

ArundhatiD · ‎11-14-2022

thank you

v-yiruan-msft · ‎11-10-2022

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

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

ArundhatiD · ‎11-11-2022

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

v-yiruan-msft · ‎11-13-2022

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

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

Rank function is not working properly. Want to see data only for last 5 sprints

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly