Problems with Rankx, Dense

[Anonymous]

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!

[mahenkj2]

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.

[lbendlin]

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-...
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/1447...

[Anonymous]

Thanks 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 drive 

[lbendlin]

There 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)