Rank with filters

[Anonymous]

Hi mates, 

I have three columns, "Company", "COuntry" and "ValueX". 

I want to rank a company (by using a filter) base on "ValueX" within a selection of countries (another filter). I used the DAX as below with the purpose to keep the country filter and remove the company filter. However, it returns the rank of that company in ALL countries 😞 

Ranking = RANKX(ALLEXCEPT(Geography,Geography[Country]),[ValueX],,ASC,Skip)

Please help. 

Thank you so much in advance. 

[Anonymous]

@CNENFRNL Hi @CNENFRNL  can we have a call for about 10 mins then I can share my screen to you? I am so desperate now 😞 

The Allselected seems does not work either 😞

[Anonymous]

Hi @CNENFRNL , thank you so much for you reple. But I cant still get you quite clearly. Could you please advise me what should I do in this case? 

[CNENFRNL]

Hi, @Anonymous , I listed a direct reason that your measure returns the rank of that company in ALL countries; ALLEXCEPT(Geography,Geography[Country]) here is used as a table function, it returns Geography table without column [Country]; natually the filter on Geography[Country] is gone with the wind...

Without a close look at your data model, I could do nothing but to make a guess at my best,

Ranking = RANKX ( ALLSELECTED ( Geography[Country] ), [ValueX],, ASC, SKIP )

[CNENFRNL]

Hi, @Anonymous , ALLEXCEPT(T, C1, C2...) is tricky,

1. When used as a table function, ALLEXCEPT materializes all the unique combinations of the columns in the table specified in the first argument that are not listed in the following arguments;

2. When used as a modifier in CALCULATE or CALCULATETABLE, ALLEXCEPT removes the filters from the expanded table specified in the first argument, keeping only the filters in the columns specified in the following arguments.

You might to refer to the link for more details.