How do I RANKX over text duplicates?

[Anonymous]

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!

[Anonymous]

Anyone?

I'm still trying using RANKX using the index column, but every row gets the value 1 for some reason..

[mahoneypat]

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

[Anonymous]

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.

[mahoneypat]

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

[Anonymous]

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.

[Anonymous]

If I were to write it in SQL:

rank() over(partition by ID, order by Description) as rank.

Hope this clarifies.

Anyone please..?

[mahoneypat]

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

[amitchandak]

@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])

[Anonymous]

@amitchandak 

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.

[CNENFRNL]

Hi, @Anonymous , with such few mockup data, I assume you'd like to seek a very similar solution like this thread

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Counting-number-of-continuos-same-entries/m-p/1439775#M26930

[mahoneypat]

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

[Anonymous]

@mahoneypat 

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