topic Re: RANKX Returns 1 Both All and ALLSELECT do not work in DAX Commands and Tips

RANKX Returns 1 Both All and ALLSELECT do not work

Anonymous — Tue, 02 Feb 2021 01:21:17 GMT

hi experienced users,

i have tried my rankx using many tips from the forum but they still return 1 and i m sure there must be something wrong somewhere. Can you please help me have a look at the issue that i have been experiencing (sorry i am unable to attach the same file, the department locked it even though i have changed the data). My goal is to rank No.Trans column and it keeps coming back with 1.

my dax is :

Ranking by No.Trans = RANKX(ALL(dataextract1),CALCULATE(COUNTX(dataextract1,dataextract1[No.Trans])),,DESC)

Thanks heaps

Re: RANKX Returns 1 Both All and ALLSELECT do not work

amitchandak — Tue, 02 Feb 2021 02:33:11 GMT

Anonymous , is card holder and unit name both from the same table. if not you can face an issue like that

Try like this and check

Ranking by No.Trans = RANKX(ALLselected(dataextract1[cardHolder],dataextract1[UnitName]),CALCULATE(COUNTX(dataextract1,dataextract1[No.Trans])),,DESC)

Re: RANKX Returns 1 Both All and ALLSELECT do not work

Anonymous — Tue, 02 Feb 2021 04:06:29 GMT

Thanks Amit,

The data is from the same table (see the screenshot) and i have tried your calculation, Still showing 1.

Re: RANKX Returns 1 Both All and ALLSELECT do not work

amitchandak — Tue, 02 Feb 2021 04:18:50 GMT

Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: RANKX Returns 1 Both All and ALLSELECT do not work

Anonymous — Tue, 02 Feb 2021 04:55:05 GMT

Thanks Amit! i am not sure if this works.. Can you let me know if you have any problem accessing

https://app.powerbi.com/groups/me/dashboards/8f02db35-5b44-4c49-8564-ed04617ec471?ctid=4e3b95cb-ee21-4049-9604-bd201ffbcf8b

Re: RANKX Returns 1 Both All and ALLSELECT do not work

Anonymous — Tue, 02 Feb 2021 05:12:01 GMT

Hi Amit,

Thankyou, i hope you can open it https://app.powerbi.com/groups/me/dashboards/8f02db35-5b44-4c49-8564-ed04617ec471?ctid=4e3b95cb-ee21-4049-9604-bd201ffbcf8b

Re: RANKX Returns 1 Both All and ALLSELECT do not work

amitchandak — Tue, 02 Feb 2021 06:02:36 GMT

Anonymous , this is on power bi service. You can share within the organization or make it public. Please upload on dropbox and onedrive and share the link.

Re: RANKX Returns 1 Both All and ALLSELECT do not work

Anonymous — Wed, 03 Feb 2021 01:00:28 GMT

Thanks Amit, I have tried all the way you suggested and also referred to the article you shared and it is still not working. The calculation that i tried is

Ranking by No.Trans = RANKX(ALLSELECTED(dataextract1[Cardholder],dataextract1[Unit Name]),CALCULATE(COUNTX(dataextract1,dataextract1[No.Trans])),,DESC)

Anyone can try and give me some advice please..

https://drive.google.com/file/d/1lz9y8--z1Bul46oJ5BfF0wpsYp3cQpu0/view?usp=drivesdk

https://drive.google.com/file/d/1LYTKq1LHiwYUiFO_DNfBqSdI13Vn6OFM/view?usp=drivesdk

Re: RANKX Returns 1 Both All and ALLSELECT do not work

Anonymous — Tue, 02 Feb 2021 06:20:01 GMT

The data looks very simple like this and all i m trying to do is to ranks the cardholder based on No.Trans. It still returns 1 for all rows.

Re: RANKX Returns 1 Both All and ALLSELECT do not work

amitchandak — Tue, 02 Feb 2021 06:52:40 GMT

Anonymous , the thing to remember is that if the only rank(measure rank) on cardholders name and use any other column, rank will be inside that. If you take table and if the cardholder is not part of the unique key it will not guarantee the correct rank

Assume No. Trans is measure = calculate(count(Table[transaction])

cardholder Rank = RANKX(all(Table[cardholder]),[No. Trans]) // with school might not work
Table Rank = RANKX(all(Table),[No. Trans]) // at lowest level
cardholder School Rank = RANKX(all(Table[cardholder],Geography[School Name]),[No. Trans]) // will work with cardholder and school name

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Re: RANKX Returns 1 Both All and ALLSELECT do not work

Anonymous — Wed, 03 Feb 2021 00:30:12 GMT

https://drive.google.com/file/d/1lz9y8--z1Bul46oJ5BfF0wpsYp3cQpu0/view?usp=drivesdk

Hi Amit, i have tried many ways and still can't attache pbix file, the department locked it even though it is just a mock data. I have used my gmail drive to save down the raw data, can you see if you can open it as i am really hoping to get the issue resolved with Ranking in powerbi. Thanks for your help!

Re: RANKX Returns 1 Both All and ALLSELECT do not work

Anonymous — Wed, 03 Feb 2021 00:32:11 GMT

Hi Amit,

i still can't send PBIX file using different ways, the department blocked it given that it is just mock data. I am using my personal gmail drive to save down the data, let me know if you can access it.

I really hope to get the Ranking in powerbi work for my case...

Mock Data