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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
BICrazy
Helper II
Helper II

Help needed with Dynamic Filtering for RANKX Function

Hi there Power BI Community,

 

I've hit a roadblock with regards to filtering whilst ranking a column

 

I have 3 Columns i.e. Countries, Region and Sub-Region which are each ranked by a value, which is working fine and gives me the ranking of the country:

Filtering by Region and Sub-Region works fine.PNG

However, when I filter the countries, what I'd like is for the region and sub-region not to default to 1.
Here is an example of the problem: I chose South Africa and region and Sub Region default to 1.
Problem when Filtering Country.PNG

What I would like to ideally see when filtering by country e.g. if I choose South Africa, the Region should display Africa as 6 and Sub-Region as 3.  Note that in the below image, I did not select South Africa.  If I do select it, then the Region and Sub-Region change to 1.
Correct view.PNG
Here are the measure's that I am using to perform the ranking of the 2 columns:
Sub-Region Ranking:

 

Sub Region Ranking = 
RANKX( 
    ALLSELECTED( Country_Name[Sub-Region] ), 
    CALCULATE( [Average Values WB])
    ,,desc, Dense)

 

Region Ranking:

 

Region Ranking = 
RANKX( 
    ALLSELECTED(Country_Name[Region] ),
    CALCULATE( [Average Values WB])
    ,,DESC, Dense
    )

 

Country Ranking:

 

Country Ranking = 
RANKX( 
    ALLSELECTED( Correct_Country_Spell[Country_Name]), 
    CALCULATE([YoY% Change since 2010])
    ,,desc, Dense 
    )

 


Hope that I've provided sufficient information for assistance and thanks in advance.

Regards

BICrazy

1 ACCEPTED SOLUTION
BICrazy
Helper II
Helper II

Hi there all,

 

Apologies for the delay in my post.  Here is the link to the pbix file which contains the solution.
Test_RankX using Measure 

Kind regards

Rakesh

View solution in original post

6 REPLIES 6
BICrazy
Helper II
Helper II

Hi there all,

 

Apologies for the delay in my post.  Here is the link to the pbix file which contains the solution.
Test_RankX using Measure 

Kind regards

Rakesh

v-janeyg-msft
Community Support
Community Support

Hi, @BICrazy 

 

I agree with them, but here I see that you haven't replied. Have you solved the problem? If not, please share some insensitive data samples and expected output. In addition, we don't know the codes of these two measures: [YoY% Change since 2010] and [Average Values WB], you also need to explain.

 

Best Regards,
Community Support Team _ Janey

 

Hi there all,

Apologies for the delayed response.  It's been a busy few weeks to the start of 2022...all Power BI related though 😊

I tried using calculated columns for this exercise but the model became too slow as the fact table contains approximately 13M row's and I had to create 3 columns, 1 for the country, 1 for the region, and another for subregion.  These +/- 40 M iterations were causing the Power BI Desktop refreshes to timeout.  

I then proceeded to build a model with measures using sample data which I tested and ended up working.  I will post the solution shortly.

Hi @BICrazy 

 

You are welcome to share your solution, then you can accept it as solution.

 

Janey

VahidDM
Super User
Super User

Hi @BICrazy 

 

Try to use calculated columns rather than measures.

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

New Year Power BI eCard:

 

https://community.powerbi.com/t5/Data-Stories-Gallery/Happy-New-Year/td-p/2266398

 

 

 

eb50dd_d85fbe053af7491e915ca41732d978a7~mv2

 

AlexisOlson
Super User
Super User

If you don't want the rank to change in response to filters, then it sounds like you want static ranking rather than dynamic ranking, in which case you may want to define rank via calculated columns rather than measures.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.