Forum Discussion

vanvir's avatar
vanvir
New Member
3 years ago
Solved

DAX - Rank duplicates in sequence

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 ...
duplicate
  • some_bih's avatar
    some_bih
    3 years ago

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

     

    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

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.