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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NickMol25
Frequent Visitor

Dynamically display Column values based on RANKX formula.

I have created some formulas which ranks countries based on a value. I have one measure which shows the rank based on sales, and then a legend column which displays the country when the country is ranked in the top 5, and displays Other if not. 

 

NickMol25_0-1617111713408.png

This works withour filters, but when I do filter, a problem arises. When I only select Europe (for example), the ranking is working fine. It displays the ranking without gaps from Europe. However, the Legend column does not dynamically change upon the change in the ranking. In the legend column, I would like the formula to show the country name of France and Denmark, since they are in the top5 ranking within Europe. 

NickMol25_1-1617111925393.png

 

Is there a way to achieve this? 

Measure = rankx(ALLSELECTED('Countries'),[Sales])
Legend = if([Measure]<=5,'Countries'[Country Name],"Other")



 

1 ACCEPTED SOLUTION

Hi, @NickMol25 

Thank you for your feedback.

I am not sure why that happened.

Can you change your legend measure like below?

Or, you can also refer to the link down below, that I just created some sample pbix file to test it.

 

Legend = IF([Measure] <= 5, SELECTEDVALUE(Data[Country]), "Other")
 
 
 
 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

7 REPLIES 7
Jihwan_Kim
Super User
Super User

Hi, @NickMol25 

 

I am not sure if it works, but please try to change your measure like below.

 

Measure = rankx(ALLSELECTED('Countries'[Country Name]),[Sales])

 

If allselected is applied to the table level, then I think continent level cannot be filtered.

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Hey @Jihwan_Kim 

 

Thanks for the suggestion. The sorting now works as it should, but when I add the legend to my table every value is changed to 1. 

 

NickMol25_0-1617115109918.png

 

I assume this happens because it is comparing per row and not the total. Any idea how I can make sure that my legend works correctly as well? 

Hi, @NickMol25 

Thank you for your feedback.

I am not sure why that happened.

Can you change your legend measure like below?

Or, you can also refer to the link down below, that I just created some sample pbix file to test it.

 

Legend = IF([Measure] <= 5, SELECTEDVALUE(Data[Country]), "Other")
 
 
 
 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Hey @Jihwan_Kim 


Works perfectly! Thank you very much. I will accept the answer as solution. 

Thank you very much. 😁

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

One additional question. In a measure everything works fine, but if I want to use this in a graph it would need to be a column. How can I make sure that this will also work? I tried the measure formula but the selectedvalue does not work over there. 

Hi, @NickMol25 

You are correct, and you have to have some kind of grouping table.

For instance, TopNCustomers rank between 1~5, and OthersCustomers rank between 6~100.

Then, in this case, the graph can show the legend.

 

I hope this helps.

Thank you very much.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.