Compare 2 columns from different tables using measure

[Anonymous]

I have data like this

Table A

YEAR REGISTERED  | SALESMAN

2017 | A

2018 | B

TABLE B

YEAR SALES | SALESMAN

2017 | A

2017 | A

2018 | A

2018 | B

If i join those table using filter following
FILTER(TABLE A, CONTAINS(TABLE B, TABLE B[YEAR SALES], TABLE A[YEAR REGISTERED])
it would appears to be like

YEAR SALES | YEAR JOIN | SALESMAN

2017 | 2017 | A

2018 | 2017 | A

2018 | 2018 | B

But I am looking for an output to be like this

YEAR SALES | YEAR JOIN | SALESMAN

2017 | 2017 | A

2018 | 2018 | B

And I could only use measure because the data source is using SSAS live connection

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try below measure:

Measure =
CALCULATE (
    MIN ( TableB[YEAR SALES] ),
    FILTER (
        TableA,
        CONTAINS ( TableB, TableB[YEAR SALES], TABLEA[YEAR REGISTERED] )
    )
)

Regards,

Cherie