Forum Discussion
How do I RANKX over text duplicates?
Hi,
I've tried some of the examples of text rankings in this forums, with little to no success.
Here's what I'd like to achieve:
My goal - filtering out all values that are different than 1 using a measure.
I can't use calculated columns because I'm using a Live Connection method over a Tabular Model.
Any help would be appreciated!
12 Replies
Is there another column that can be used to tell which row is first (index, datetime, etc.)? That will make it much easier to write the needed DAX measure.
Regards,
Pat
Hi,
Ive found another unique column I can sort this by. What would the syntax be like?
I tried the suggestions below here to no avail..
Hi, Anonymous , with such few mockup data, I assume you'd like to seek a very similar solution like this thread
Anonymous , As suggested by mahoneypat , Add an index column and try a formula like
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
countx(filter(Table, [Index] <=earlier([index]) && [ID] = earlier([ID])),[ID])
Hi, thanks for the help but I can't use "Edit Queries", because I'm using live connection, which disables that feature.
I also tried the COUNTX example but PBI throws an error, saying the syntax is wrong.
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.
