Forum Discussion
How do I RANKX over text duplicates?
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.
If I were to write it in SQL:
rank() over(partition by ID, order by Description) as rank.
Hope this clarifies.
Anyone please..?
One more suggestion. Since you don't care with the three 123 text values is displayed, I would just make a table visual with the ID column and this measure
Text Values = MIN(Table[Description])
That will result in one row per ID and just one of the Description values.
Regards,
Pat