topic Re: Getting Rank of Text Column which has duplicate records in DAX Commands and Tips

Getting Rank of Text Column which has duplicate records

Anonymous — Tue, 18 Feb 2020 07:49:48 GMT

I am not sure how to mention this. I have a simple table as below. I am trying to get the rank basis on 'Name' Column which has duplicate record. Can any one help whether this can be achieved in power bi? Thanks in Advance.

Name	Desired Output (Rank)
A	1
A	2
A	3
A	4
B	1
B	2
B	3
C	1
C	2
C	3
C	4
C	5
C	6

Re: Getting Rank of Text Column which has duplicate records

az38 — Tue, 18 Feb 2020 08:19:18 GMT

Hi Anonymous

the easiest way is to add a Custom Index Column in Power Query Editor mode

then try to create a calculated DAX column like

RANK = var _RowsBefore = COUNTROWS(FILTER('Table', 'Table'[Name]<EARLIER('Table'[Name]))) RETURN rankx(FILTER('Table', 'Table'[Name]=EARLIER('Table'[Name])), 'Table'[Index],,ASC) + _RowsBefore

Re: Getting Rank of Text Column which has duplicate records

Anonymous — Tue, 18 Feb 2020 08:48:42 GMT

Hi az38

I tried the way you mentioned however its producing rank for all. Please see below-

Re: Getting Rank of Text Column which has duplicate records

MartynRamsden — Tue, 18 Feb 2020 10:19:17 GMT

Hi Anonymous

Once you've added your index column, you can use the DAX expression below to create your rank column:

Rank = VAR RowName = Table1[Name] VAR Result = RANKX( FILTER( ALL( Table1 ), Table1[Name] = RowName ), Table1[Index], ,ASC ) RETURN Result

Best regards,
Martyn

Re: Getting Rank of Text Column which has duplicate records

Anonymous — Tue, 18 Feb 2020 10:47:16 GMT

Create one index column and then create one more column as below.

Rank = CALCULATE(COUNT('Table'[Col]),FILTER('Table','Table'[Col]=EARLIER('Table'[Col]) && 'Table'[Index]<=EARLIER('Table'[Index])))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: Getting Rank of Text Column which has duplicate records

Anonymous — Tue, 18 Feb 2020 11:12:24 GMT

Hi MartynRamsden

Perfect! This answers my query. Thanks a lot for your help.

Regards!

Re: Getting Rank of Text Column which has duplicate records

Anonymous — Tue, 18 Feb 2020 11:14:56 GMT

Hi Anonymous

Perfect, This is also a solution to my concern. Thanks a lot for your help.

Regards,

Re: Getting Rank of Text Column which has duplicate records

Tani4ka — Wed, 30 Jun 2021 09:17:25 GMT

I have a similar problem with ranking. What I need is to have all channel_grouping_1 have the same rank. Offline Only 1 across all lines, Direct 2 across all lines etc. Can anyone help?

Many thanks