Forum Discussion

Jo5hua22's avatar
Jo5hua22
Helper I
5 years ago
Solved

Compare duplicated data from RANKX with another column

Hi members,

 

I would require your support to compare duplicated data that is obtained via RANKX function

 

I used RANKX to create an index based on another column (Date) using DAX command

 

Now this column has duplicated values. I would like to compare values with another column if there is duplication and then I would like to Rank this column.

 

Example:

Expected calculation:

Step 1: Date has to be sorted in ascending order;
Step 2: Then if the index values are same, they should be sorted according to the ascending order of the machine number

Raw input   
S.NoDateMachine numberCreated Index value
A121201.08.202151
A121307.08.202113
A121401.08.202131
A121506.08.202152
A121608.08.202124
A121701.08.202111
Expected calculation   
A121701.08.202111
A121401.08.202132
A121201.08.202153
A121506.08.202154
A121307.08.202115
A121608.08.202126
Dataflow
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Jo5hua22 

    Please try formulas as below:

    Created Index value = RANKX('Table','Table'[Date],,ASC,Dense)
    machine number Ranked = RANKX('Table','Table'[S.No],,DESC,Dense)
    Result = 
VAR machine_ranked =
    MAX ( 'Table'[machine number Ranked] ) //Maximum  value of sub-column(machine)=5
VAR result =
    RANKX (
        ALL ( 'Table' ),
        'Table'[Created Index value] * machine_ranked + 'Table'[machine number Ranked],   
        //Rank of the main column * 5+ the rank of the sub-column
        ,
        ASC,
        DENSE
    )
RETURN
    result

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies