DAX - Rank duplicates in sequence

[vanvir]

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.

[Anonymous]

Hi @vanvir ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[some_bih · Accepted solution]

Hi @vanvir for possible solution, please create two columns, Index and Count Formula.

Index column should be created by Power Query, choose From 1.

Second column, Count Formula is calculated column with formula below. Output is same as yours.

Did I answer your question? Kudos appreciated / accept solution!

Link for how to do it below 

https://support.microsoft.com/en-us/office/add-an-index-column-power-query-dc582eaf-e757-4b39-98e6-b... 

Count Formula =

VAR __currentvalue=Sheet1[Type]

VAR __current_index=Sheet1[Index]

VAR _count_filtering=

CALCULATE(

    COUNTROWS(

        FILTER(

            Sheet1,

            Sheet1[Type]=__currentvalue

        )

    ),

    ALLEXCEPT(Sheet1, Sheet1[Type]),

    Sheet1[Index]<=__current_index

)

VAR _Result=

_count_filtering-1 --forcing to count 0 (alternative change Index to 0)

RETURN _Result

Output