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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.