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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.