Compare duplicated data from RANKX with another column

[Jo5hua22]

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.No	Date	Machine number	Created Index value
A1212	01.08.2021	5	1
A1213	07.08.2021	1	3
A1214	01.08.2021	3	1
A1215	06.08.2021	5	2
A1216	08.08.2021	2	4
A1217	01.08.2021	1	1
Expected calculation
A1217	01.08.2021	1	1
A1214	01.08.2021	3	2
A1212	01.08.2021	5	3
A1215	06.08.2021	5	4
A1213	07.08.2021	1	5
A1216	08.08.2021	2	6

[v-easonf-msft · Accepted solution]

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.

[sevenhills]

Please post your DAX to know the issue ... (Always, issue, data, code, expected output helps the community to answer)

It looks like you are trying to do is SQL's DENSE_RANK. 

Check these two articles

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

https://community.powerbi.com/t5/Desktop/Rank-based-on-date-for-each-individual-user/td-p/411053

Thanks