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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rhouse
Helper I
Helper I

RANKX Assigning Duplicate Values

I am having trouble using the RANKX function. I have two tables. One is a table with inventory values that contains the measure INV $ (The measure is just a sum of the inventory $ column). The other is a dimensional table containing the item numbers and other descriptive information about each item we sell. These two tables are joined on an item key. 

 

I have a table in Power BI that shows the measure INV $ grouped by Item Family. I have shown a sample table below. 

 

Family          INV $               Rank

Family 1       $1,000,000        1

Family 2       $998,000           2

Family 3       $997,000           3

Family 4       $996,000           3

Family 5       $995,000           5

Family 6       $994,000           5

 

 

The formula I am using is  

Rank = RANKX(ALLSELECTED('Dim Item'[Family]), [INV $],,DESC,Dense)

 

For some reason, it is duplicating ranks for families that clearly have different values for INV $. I know this may be hard without seeing the actual data and file, but does anyone have any idea what I am doing wrong?

 

Thanks!

6 REPLIES 6
Anonymous
Not applicable

For those having this issue I was able to solve it with the below formula:

[Rank] := 
IF (
HASONEVALUE ( 'Dim Item'[Family] ), 
RANKX(ALLSELECTED('Dim Item'[Family]), [INV $],,DESC,Dense)
)

I have no clue why IF HASONEVALUE made the difference however, I've posted the question to the original site:
https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/

J-Bo
New Member

Has there been any solution to this issue? I am also experience the same problem with RANKX.

I have the same issue, please help

v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @rhouse,

 

Could you try the formula below to see if it works? Smiley Happy

 

Rank = RANKX ( ALL ( 'Dim Item' ), [INV $],, DESC, DENSE )

 

Regards

Thanks for the reply! I tried it, but unfortunately that did not work. I ended up with larger chunks of duplicate ranks. I should also mention I do have other filters on the item table in the report that I need the rank to take into consideration, so I am not sure if ALL is what I need or not. I am just starting to learn more complicated DAX though, so sometimes I get confused.

Hi @rhouse,

 

Could you post your table structures(including table relationships) with some sample data, so that we can help further investigate on this issue? It's better that you can also share a sample pbix file if possible. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

 

Regards

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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