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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hvdb6
Regular Visitor

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!

 

rank.PNG

 

4 REPLIES 4
mahenkj2
Impactful Individual
Impactful Individual

Hi @hvdb6 

 

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
Super User
Super User

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

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 

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)

 

lbendlin_0-1686610379957.png

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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