Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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.
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)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |