Don't change the previous rank based on selection

[akhilduvvuru]

Hi, I have 20 students and marks related data by semester where I want to show a table in Power BI with these fields Student Name, Current Semester Rank, Previous Semester Rank, Current Semester Marks, Previous Semester Marks. 

I should also need a Top N slicer in the dashboard where we should be able to filter the Top students by Current Semester Rank.

I have created 2 rank measures based on Current Semester Marks and Previous Semester Marks as following.

Current Semester Rank = RANKX(ALLSELECTED(STUDENTSTABLE[Student Name]), SUM(STUDENTTABLE[Current Semester Marks]),, DESC, Dense)

Previous Semester Rank = RANKX(ALLSELECTED(STUDENTSTABLE[Student Name]), SUM(STUDENTTABLE[Previous Semester Marks]),, DESC, Dense)

Using the above Rank mesures, I'm getting the right result when I show the data for all the students.

Student Name	Current Semester Rank	Prev Semester Rank	Prev Semester Marks	Current Semester Marks
Alex	1	4	924	954
Bobby	2	7	889	930
Jacob	3	1	956	910
Michael	4	3	927	900
Andrew	5	16	833	890
Mathew	6	17	828	850
Austin	7	12	853	840
David	8	10	873	835
Chris	9	13	851	832
Nick	10	5	922	827
Nathan	11	6	890	824
Anthony	12	20	808	820
Victoria	13	15	841	818
Brittany	14	18	824	815
Jasmine	15	9	881	812
Emilu	16	14	843	810
Jessica	17	8	886	809
Emma	18	11	872	808
Rachel	19	2	952	805
Magen	20	19	810	804

However the issue is with Top N slicer. When I apply the Top N slicer to show only Top 5, Andrew's Prev Semester Rank was 16 (changed to 5) also Bobby's Prev Semester Rank was 5 (changed to 2).

Student Name	Current Semester Rank	Prev Semester Rank	Prev Semester Marks	Current Semester Marks
Alex	1	4	924	954
Bobby	2	2	889	930
Jacob	3	1	956	910
Michael	4	3	927	900
Andrew	5	5	833	890

Same with Top 10 as well, Bobby was at 7 (changed to 2), Andrew was at 16 (changed to 5), Mathew was at 17 (changed to 9) etc.,

Student Name	Current Semester Rank	Prev Semester Rank	Prev Semester Marks	Current Semester Marks
Alex	1	4	924	954
Bobby	2	2	889	930
Jacob	3	1	956	910
Michael	4	3	927	900
Andrew	5	5	833	890
Mathew	6	9	828	850
Austin	7	8	853	840
David	8	10	873	835
Chris	9	7	851	832
Nick	10	5	922	827

One observation is - It islimiting the Prev Semester Ranks within the Top N slicer value.

I don't want the Prev Semester Rank to change. Not sure how to handle this. Can someone please help with this?

Thanks!

[Anonymous]

Hi @akhilduvvuru ,

Try to change the measure into these:

Current Semester Rank = 
RANKX(ALL(STUDENTSTABLE), 'STUDENTSTABLE'[Current Semester Marks], MAX('STUDENTSTABLE'[Current Semester Marks]), DESC, Dense)

Previous Semester Rank = 
RANKX(ALL(STUDENTSTABLE), 'STUDENTSTABLE'[Prev Semester Marks], MAX('STUDENTSTABLE'[Prev Semester Marks]), DESC, Dense)

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[akhilduvvuru]

Hi @Anonymous Thanks for the response.

"Current Semester Marks" and "Prev Semester Marks" which I mentioned are measures which I created using a field called Marks. Hence, can't directly use them as you mentioned in your response.

for example: 

Current Semester Marks = CALCULATE(SUM(STUDENTSTABLE[Marks]), STUDENTSTABLE[Grade] = "Two", STUDENTSTABLE[Status] = "Active", STUDENTSTABLE[QuarterFlag} = "Current Quarter")

Previous Semester Marks = CALCULATE(SUM(STUDENTSTABLE[Marks]), STUDENTSTABLE[Grade] = "Two", STUDENTSTABLE[Status] = "Active", STUDENTSTABLE[QuarterFlag} = "Previous Quarter")

[Anonymous · Accepted solution]

Hi @akhilduvvuru ,

I think that's the problem, because your Current Semester Marks and Previous Semester Marks are both measures, so when you use slicer for filtering, not only are the 2 Rank measures affected by slicer, but so are both measures will also be affected by slicer. 
When you slicer filter the Top5, your two Marks measures, because they are also affected by the slicer, cause them to return only the values corresponding to the 5 students, so the subsequent Rank will only sort on those 5 values instead of on all values.
Please change your DAXs into these and it should be OK:

Current Semester Rank = RANKX(ALL(STUDENTSTABLE), 'STUDENTSTABLE'[Current Semester Marks], , DESC, Dense)

Previous Semester Rank = RANKX(ALL(STUDENTSTABLE), 'STUDENTSTABLE'[Previous Semester Marks], , DESC, Dense)

Output:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande]

@akhilduvvuru  

You can adjust your DAX measures by using ALL to ignore the slicer filter context for "Previous Semester Rank."

Current Semester Rank (No change needed here):

Current Semester Rank = 
RANKX(ALLSELECTED(STUDENTSTABLE[Student Name]), SUM(STUDENTTABLE[Current Semester Marks]),, DESC, DENSE)

Previous Semester Rank (Modified to ignore Top N filter):

Previous Semester Rank = 
RANKX(
ALL(STUDENTSTABLE[Student Name]), 
SUM(STUDENTTABLE[Previous Semester Marks]),
, DESC, DENSE
)

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande

[akhilduvvuru]

@Kedar_Pande - Thanks for the quick response. I tried that already but the same rank is repeating for few students even the marks are different between them.

[Kedar_Pande]

Can you share the file please.

Upload in drive and share the link here

[rajendraongole1]

Hi @akhilduvvuru - Instead of using a measure for the "Previous Semester Rank," you can create a calculated column for the rank based on all students, which will not be affected by the Top N filter.

Previous Semester Rank (Static) =
RANKX(
ALL(STUDENTTABLE[Student Name]),
CALCULATE(SUM(STUDENTTABLE[Previous Semester Marks])),
,
DESC,
DENSE
)

You can still use the measure for the "Current Semester Rank" since this should dynamically change based on the Top N slicer

Current Semester Rank =
RANKX(
ALLSELECTED(STUDENTTABLE[Student Name]),
SUM(STUDENTTABLE[Current Semester Marks]),
,
DESC,
DENSE
)

Add a slicer for the Top N students, but now the "Previous Semester Rank" will remain consistent even when filtering the top students based on "Current Semester Rank."

add the visual and check the visual by adding the fields 

Hope this helps.

[akhilduvvuru]

@rajendraongole1 - Thanks for your quick response. While creating the Prev Semester Rank column, I'm getting a circular dependency error with one other calculated column which I had created in the same table for some other usecase.

[akhilduvvuru]

@Ritaf1983 @rajendraongole1 @lbendlin @dharmendars007 @Kedar_Pande @Greg_Deckler @Ashish_Mathur @amitchandak Much appriciated your help here. Thanks!

[OktayPamuk80]

Hi,

If the data should not be dynamic relating to ranking, the solution would be having ranking pre calculated in power query e.g using index and custom columns. Issue is as you stated, that the calculated values change, because calculation instantly takes place based on the selection, which you want to avoid.

Does this help you?

Regards,
Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[akhilduvvuru]

Hey @OktayPamuk80 - Thanks for your response.

I cannot do it in the Power Query. My doubt is even though the semester marks are not changing, why is my rank changing?