Power BI RANKX with filter and calculate max causing circular dependency

I'm wondering if anyone can help me overcome a little problem I'm experiencing with RANKX in power bi.

I have a table of data with a couple of columns containing duplicate values,

I want to apply a rank number to each value in the table, with the idea of being able to be filter the column on rank "1", which should if my logic is correct, show me all the unique data,

I've added an index column to the table to allow me to rank against,

There are over 5 million rows of data in my table, below is a sample of the data table where I have filtered the HUB_ID on 2 specific duplicate values (just for the purposes of this exercise):

enter image description here

the above shows rows with the duplicated data against the HUB_ID, so what I'm looking for is to apply a ranking to the index based on the HUB_ID.

so that I get the following:

enter image description here

Apologies that I've not sorted the RANK column into numerical order but hopefully you can understand what I'm attempting to do all the same :o)

The data sits in a table called Macquarie-Database and I have attempted to create a new column with the following:

RANK =
RANKX (
    FILTER (
        'Macquarie-Database',
        'Macquarie-Database'[HUB_ID] = EARLIER ( 'Macquarie-Database'[HUB_ID] )
    ),
    CALCULATE ( MAX ( 'Macquarie-Database'[Index] ) ),
    ,
    ASC,
    SKIP
)

However I keep getting the error "A circular dependency was detected: Macquarie-Database[RANK]"

I've used RANKX in exactly same way in other power bi reports which has worked fine, so i'm scratching my head as to why suddenly now I'm getting the error.

Maybe there's a better way to do what I need, I don't know but was hoping that you good people on the forum may be able to help me sort out my rank? :o)

Any help at this initial stage would be greatly appreciated, many thanks in advance

Regards

I have tried:

RANK =
RANKX (
    FILTER (
        'Macquarie-Database',
        'Macquarie-Database'[HUB_ID] = EARLIER ( 'Macquarie-Database'[HUB_ID] )
    ),
    CALCULATE ( MAX ( 'Macquarie-Database'[Index] ) ),
    ,
    ASC,
    SKIP
)

However I keep getting the error "A circular dependency was detected: Macquarie-Database[RANK]"

This has worked successfully with previous reports, however for some reason with this latest report I'm getting the error message.

I'm hoping to get a RANK column on the end of my table which ranks (ASCENDING) as per the index value against a HUB_ID:

When this didn't work I then tried:

How to generate rank in Power BI on the basis of two column

RANK_HUB_ID =
RANKX (
    ALL ( 'Macquarie-Database' ),
    'Macquarie-Database'[HUB_ID],
    ,
    ASC,
    DENSE
)

which produced:

enter image description here

and then created a new column with the following:

RANK =
VAR X =
    MAX ( 'Macquarie-Database'[RANK_HUB_ID] )
VAR RESULT =
    RANKX (
        ALL ( 'Macquarie-Database' ),
        'Macquarie-Database'[Index] * X + 'Macquarie-Database'[RANK_HUB_ID],
        ,
        ASC
    )
RETURN
    RESULT

However, this results in the following:

enter image description here

So I'm really not sure now where to go with this :o(

Can anyone help please?

Is this what you want? Rank2 = RANKX(FILTER(demo, demo[HUB_ID] = EARLIER(demo[HUB_ID])), demo[Index], , ASC) See results in the photo attached https://i.sstatic.net/Jfc1in52.png