Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
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/
Has there been any solution to this issue? I am also experience the same problem with RANKX.
I have the same issue, please help
Hi @rhouse,
Could you try the formula below to see if it works?
Rank = RANKX ( ALL ( 'Dim Item' ), [INV $],, DESC, DENSE )
Regards
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.
Regards
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.