Forum Discussion
How do I RANKX over text duplicates?
Anyone?
I'm still trying using RANKX using the index column, but every row gets the value 1 for some reason..
Can you describe more about the analysis you plan to do once you have the desired column? I expect there is a different approach with DAX that you get your result with a live connection.
Regards,
Pat
As I've mentioned in my first message - my intention is to only show the rows with the value 1 (after ranking). I don't need to display the duplicates, any instance of a certain ID is enough for my analysis.
So if this is my data:
ID | Desc
123 text1
123 text2
456 text1
I don't care which row of ID 123 gets rank #1.
I want to rank my entire table so every ID is displayed one time only. Super easy for me in SQL but seems impossible in DAX.
It probably isn't necessary to add the rank column. For example, in a table visual you can just add the ID and Text columns and it will show only the unique combination of those columns (no duplicates). Similarly, if you make a virtual table with SUMMARIZE(Table, Table[ID], Table[text]), you can do analysis on/from that (no duplicates). Please decribe further what analysis you plan to do.
Regards,
Pat
Thank you mahoneypat for bearing with me, but I feel like I'm not explaining myself correctly.
I do have unique rows in my table visual. If you could please take a look at the image I added - the ID '123' has three different text values. Each row of the three is unique, but I don't care for the other two.
This is why I wanted to use a rank function, so I'd rank by ID in ascending order and filter out values that are greater than 1.