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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

RANKX Returns 1 Both All and ALLSELECT do not work

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 heapsCapture.PNG
 
 

 

11 REPLIES 11
Anonymous
Not applicable

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 

Anonymous
Not applicable

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!

Anonymous
Not applicable

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.

 

Capture.PNG

@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

Anonymous
Not applicable

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..
Anonymous
Not applicable
Anonymous
Not applicable

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...

@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.

amitchandak
Super User
Super User

@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)

Anonymous
Not applicable

Thanks Amit,

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors