Powerpivot dax RANKX using two separate tables

Question

I have two tables I am bringing into powerpivot via ODBC and powerquery and ranking using the below. In the case of a tiebreak I'm using RAND () and then regenerating.

The problem I am having is I now have a second table that I need to also incorporate into my RANK and unsure how to do a CROSSJOIN or create a measure that will evaluate both tables in my data model. Currently ive done as a calculated column.

Can anyone help?

=
RANKX (
    FILTER ( 'Table1', [Reference_ID] = EARLIER ( 'Table1'[Reference_ID] ) ),
    'Table1'[Date], , DESC, DENSE
)

Table 1

Partner Reference ID    Date
Bank of America R1290   1/1/2018
Bank of America R1291   1/2/2018
Bank of America R1291   1/3/2018

Table 2

Partner Reference ID    Date
Bank of America R1290   1/2/2018
Bank of America R1291   1/5/2018
Bank of America R1291   1/6/2018

Alexis Olson Alexis Olson · Accepted Answer · 2018-09-07T17:13:14

You don't want to CROSSJOIN; you want to UNION.

Rank =
VAR TableUnion =
    UNION (
        SELECTCOLUMNS ( Table1, "RefID", Table1[Reference_ID], "Date", Table1[Date] ),
        SELECTCOLUMNS ( Table2, "RefID", Table2[Reference_ID], "Date", Table2[Date] )
    )
RETURN
    RANKX ( FILTER ( TableUnion, [RefID] = [Reference_ID] ), [Date], , DESC, DENSE )

Powerpivot dax RANKX using two separate tables

1 Answers