Question on RANKX formula and selected value

[kobkabnaja]

Hi community,

I have a question about RANKX formula. I am using it to rank states based on net population inflow. The formula I used is shown below, and the results appear to be correct (see attached).

FormulaResult

However, when I filtered for a single state, the ranking reset to 1 for evey state instead of retaining the original rankings from the previous table. I am not sure what went wrong and would appreciate any suggestions on how to resolve this.

Thank you,

K.

[v-junyant-msft]

Hi @kobkabnaja ,

Thanks for Tutu_in_YYC's reply!
And @kobkabnaja , Please change ALLSELECTED('migration') in your original DAX to ALL('migration'), and do not add any fields after 'migration'.
That is:

RANK_NETFLOWTOT = 
RANKX(
    ALL(migration),
    CALCULATE(SUM(migration[NETFLOW_TOT])),
    ,
    DESC,
    Dense
)

Output:

Because you have placed other fields in your visual object and you also have two slicers, using ALLSELECTED or ALL for a certain field will still cause the measure to be affected by the filtering of other fields and slicers in the visual object, resulting in changes in the results.

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.

[kobkabnaja]

Hi @v-junyant-msft, thannks for chiming in. I have tried that and it still does not work out. It ranked all the states regardless of the year. Please see the attached for your reference.

I plan to share the .pbix file, but I could not find the place to attache the file here.

Do you think it is because I cannot rank all the state by year properly? Ideally, if I can rank all the states by year first, I can pull the correct rank using the state and year. Is that the case?

Thank you,

K.

[Tutu_in_YYC · Accepted solution]

@v-junyant-msft is correct. These 2 have "Dont Summarize", and it is parsing out the states further.

If you remove them, or change them to sum or a different aggregation, Rank would work again using:

RANKX(
    ALL(migrationt[Name]),
    CALCULATE(SUM(migration[netflow_tot]))
)

[kobkabnaja]

Wow, I definitely would not have caught this issue without the help from you guys. Thank you so much!

I fixed the columns by changing them to summarize, but the ranks are now 1 and 2 in stead of 1 and 50. lol. Did I do something wrong here? Would you mind editing the file and share it back with me. I truly appreciate your help!

Thank you,
K.

[Tutu_in_YYC]

Let me know if it doesnt satisfy the requirements:

https://drive.google.com/file/d/1F88VbqmPSrWcm5J_KG-RacJojI_RNRtl/view?usp=sharing

[kobkabnaja]

It worked! Could you please share with me how you adjusted it? I tried to follow your formula in the file I sent you, but it still did not work 😞

Is it because my power BI set something up differently?

Best,

K.

[kobkabnaja]

My bad! I think I know what you mean now!!! At first, I thought you want me to change from "Dont Summarize" to "Sum" in the model part. I just changed it in the visualization and it works now. Thank you so so much for being patient with me and help me until I figure this out!!!

[Tutu_in_YYC]

😄 all good! Glad you got it!

[Tutu_in_YYC]

you can use 3rd party sharing, like dropbox to share your file. 

It should work no problem with the year column too...This is mind boggling 😄

[kobkabnaja]

Ikr. It really is!!! Especially when I follow everything stricly and I still could not solve the problem.

Thanks for your suggestion. Here is a link you can download from my GG drive:
https://drive.google.com/file/d/1Gg8fmQIoeNG3xI2vPU_0Mqc_2PenINvA/view?usp=share_link

Thank you,

K.

[Tutu_in_YYC]

Try this..

*edited to add Calculate in orange

RANKX(
    ALL(migrationt[Name]),
    CALCULATE(SUM(migration[netflow_tot]))
)

[kobkabnaja]

Hi @Tutu_in_YYC ,

Thanks for your prompt response. I did try that formula. But it did solve the problem. When I selected only one state, it still showed rank 1.

I would appreciate any further suggestions you may have.

Best,

K.

[Tutu_in_YYC]

interesting... we tried on my end, and it works... see below:

after selection

[kobkabnaja]

Thanks for your comfirmation. I tried both [YEAR], and [STATE] (or NAME) in the ALL('migration'[Name]). Both give me inccorect values, either 1 or 50. And when I took the CALCULATE out, all the values beccame 1 (I did not show it in the attached).

I am wondering if there is something that I have to do with the year filter.

Thank you,

K.

 

[Tutu_in_YYC]

Apologies, you do need the calculate:

RANKX(
    ALL(migrationt[Name]),
    CALCULATE(SUM(migration[netflow_tot]))
)

Does it work with the calculate? 

Can you share the pbix with sample data?

[kobkabnaja]

It does not. The rank showed only the first (1) and the last (50). Amazingly, when I tried to create a sample data and used your code, it worked well!!! This is so confusing to me. Do you have any other ideas why this happened?

Thank you,

K.

[Tutu_in_YYC]

I tried to replicate your error, but no dice. Can you share a screenshot of your model view? Maybe theres something we can identify in there

[kobkabnaja]

Definitely! though I am not sure if it would be much help here. I compile everything in just one table, so there is no relationship between the datasets at all (please see the attached).

Can you think of any other places that the error could occur?

Best,

K.

[Tutu_in_YYC]

Yeah, that doesnt help much 😛

Am i correct to assume, that there are distinct states 50 in your data?