RANKX for table visual with fields from multiple source tables

[MitSom_1]

Hi all, this may sound really simple but I have been struggling with it.

I want add a rank measure in my table to give the rank of SupplierCode using the Points field, like this:

But then I also want to add in an Area field that comes from a different source table, but when I do this, it messes up the ranking (I want it to ignore the Area for the ranking, but just rank Supplier code):

Any idea what DAX code I need to use to get this? Any help would be very much appreciated.

[Anonymous · Accepted solution]

Hi @MitSom_1 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:
Table1:

Table2:

Relationship:

1.Create a new table which add table 1 followed by table 2's AREA column

Table =
ADDCOLUMNS (
    table1,
    "Area", LOOKUPVALUE ( table2[Area], table2[SupplierCode], table1[SupplierCode] )
)

2.Rank by Points column(Create a new column)

Rank = RANKX(ALL('Table'), 'Table'[Points])

3.Final output

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly