topic Re: How do I RANKX over text duplicates? in DAX Commands and Tips

How do I RANKX over text duplicates?

Anonymous — Mon, 19 Oct 2020 07:44:42 GMT

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!

Re: How do I RANKX over text duplicates?

mahoneypat — Sun, 18 Oct 2020 15:20:54 GMT

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

Re: How do I RANKX over text duplicates?

CNENFRNL — Sun, 18 Oct 2020 18:18:18 GMT

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

Re: How do I RANKX over text duplicates?

amitchandak — Mon, 19 Oct 2020 02:22:40 GMT

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

Re: How do I RANKX over text duplicates?

Anonymous — Mon, 19 Oct 2020 08:25:45 GMT

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

Re: How do I RANKX over text duplicates?

Anonymous — Mon, 19 Oct 2020 08:34:56 GMT

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.

Re: How do I RANKX over text duplicates?

Anonymous — Mon, 19 Oct 2020 11:52:02 GMT

Anyone?

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

Re: How do I RANKX over text duplicates?

mahoneypat — Mon, 19 Oct 2020 12:01:07 GMT

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

Re: How do I RANKX over text duplicates?

Anonymous — Mon, 19 Oct 2020 12:25:35 GMT

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.

Re: How do I RANKX over text duplicates?

mahoneypat — Mon, 19 Oct 2020 12:36:20 GMT

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

Re: How do I RANKX over text duplicates?

Anonymous — Mon, 19 Oct 2020 12:49:49 GMT

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.

Re: How do I RANKX over text duplicates?

Anonymous — Mon, 19 Oct 2020 21:46:25 GMT

If I were to write it in SQL:

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

Hope this clarifies.

Anyone please..?

Re: How do I RANKX over text duplicates?

mahoneypat — Mon, 19 Oct 2020 21:58:47 GMT

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