DAX - Rank duplicates in sequence

Question

Hi experts group, I have searched the forum, but can't find the solution on my issue.

I need your help to write DAX code on how to rank the duplicates in sequence.

This is the data and I'd like to achieve the result as below in Count Duplicate column

Type	Count duplicate
a	0
a	1
b	0
c	0
b	1
c	1
c	2
a	2

The aim is to keep the 1st duplicates and remove the rest of duplicates using filter.

Thank you.

some_bih · Accepted Answer

Hi&nbsp;vanvir&nbsp;for possible solution, please create two columns, Index and&nbsp;Count Formula.Index column should be created by Power Query, choose&nbsp;From 1.Second column,&nbsp;Count Formula is calculated column with formula below. Output is same as yours.&nbsp;Did I answer your question? Kudos appreciated / accept solution!Link for how to do it below&nbsp;https://support.microsoft.com/en-us/office/add-an-index-column-power-query-dc582eaf-e757-4b39-98e6-bb59ae44aa82&nbsp;&nbsp;Count Formula = VAR __currentvalue=Sheet1[Type]VAR __current_index=Sheet1[Index]VAR _count_filtering=CALCULATE(&nbsp; &nbsp; COUNTROWS(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sheet1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sheet1[Type]=__currentvalue&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ), &nbsp; &nbsp; ALLEXCEPT(Sheet1, Sheet1[Type]),&nbsp; &nbsp; Sheet1[Index]&lt;=__current_index)VAR _Result=_count_filtering-1 --forcing to count 0 (alternative change Index to 0)RETURN _Result&nbsp;Output&nbsp;

Forum Discussion

DAX - Rank duplicates in sequence

2 Replies

Related content

Duplicate ranking

Rank Duplicates values in a sequence

Why rankx function shows duplicated rank?

RANKX Formula Returning Duplicate Ranks

Remove Duplicate Ranking