Forum Discussion
Problems with Rankx, Dense
Hi all:
This is my first post, so I appreciate y'alls help here.
I thought I have a fairly simple problem. But not so much...See underneath for a report. What I need is just a column called rank that starts with 1 and assigns n to whatever the highest value of the column Manual Override_DAX is. You can see that certainly several values of the column have the same value, but I need them all to have a unique rank. My tie breaker is column RECID (to the very left in the report).
Furthermore, there are BLANKs in the column Manual Override_DAX which should be getting a BLANK in the rank function also.
Lastly, Manual Override_DAX vales scale from negative into positive values (that really is the issue I can't get solved)...
Thanks everyone!
4 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thanks for flagging. Underneath please find the link to desired outcome. Please let me know your thoughts on how to achieve the rank with a DAX function (not a measure, because I want to visualize it as a ficticious datapoint on the x-axis). Note the Blanks at the bottom. Thank you!
Link to csv on google driveThere are some slight disagreements with your desired ranking but this formula is good enough:
Rank = rankx(filter(ALL('data (4)'),not ISBLANK([Value])),[Value]+VALUE([RECID])/1000000000000,,ASC,Dense)
Hi Anonymous
At first, in power query, sort the table with two columns, Manual Override_DAX and RECID (both together) and then index it.
Then in the Data view, sort the table by index.
Then use following formula:
NewRank = if (ISBLANK(data[Manual Override_DAX])=TRUE(), blank(), COUNTROWS( FILTER( VALUES(data[Manual Override_DAX]), data[Manual Override_DAX]< EARLIER(data[Manual Override_DAX])) )+1 )NewRank would be different and lower than your example rank, becuase same values are ranked same number.
https://drive.google.com/file/d/1oH_LTz9g1vbAa4yhI3n1qVFfxMAEZAGd/view?usp=sharing
Hope it helps.
