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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Kirk_1
Frequent Visitor

RANKX(ALL SELECTED… DAX causing duplicates

Here is my DAX expression:
$ RANK = RANKX(ALLSELECTED('Store-Level'), CALCULATE(SUM('Store-Level'[$])), ,DESC)

 

This DAX works in my smaller filtered table (right), but I have another larger filtered table (left) with 175,000 rows and I am getting duplicate ranking numbers. The overall data has 500,000 rows.

 

Is there a limit to how many rows a RANKX ALLSELECTED DAX formula will handle? Is there a better way to write the DAX?

 

Link to sample PBI file 7.6MB edited to exclude identifiable product info. Thanks!

https://we.tl/t-2cqgLnGP4R 

4 REPLIES 4
ThxAlot
Super User
Super User

ALLSELECTED(Table) and ALLSELECTED(Table[Column]) are totally different. There's no point ranking every row of the table by ALLSELECTD(Table).



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



The actual dataset is about 875,000 rows, but there is a filter on the entire tap that filters out customers down to a specific customer. This filters it down to about 500,000.  I am trying to get the ranking column on the 500K rows, but also want it to re-rank based on various filters down to brand, category, etc.

Kirk_1
Frequent Visitor

Manoj Nair,

It did take a lot longer to process using that, but unfortunately it did not work. It still shows duplicated ranks, like the majority of the top $ sales items have a "1" as the the rank number.

Manoj_Nair
Solution Supplier
Solution Supplier

@Kirk_1- Try this

 

$ RANK = 
RANKX(
    ALLSELECTED('Store-Level'), 
    CALCULATE(SUM('Store-Level'[$])) + COUNT('Store-Level'[StoreId]) / 1000000,
    ,
    DESC
)

 

In this revised measure, the bridge is created by adding a very small value to the sum. The value is so small (the count divided by 1,000,000) that it doesn't affect the overall sum, but it is enough to make each sum unique, which prevents the RANKX function from assigning the same rank to different rows.

 

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

 

Best Regards

 

Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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