Forum Discussion

MitSom_1's avatar
MitSom_1
Regular Visitor
2 years ago
Solved

RANKX for table visual with fields from multiple source tables

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's avatar
    Anonymous
    2 years ago

    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
     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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