Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Compare 2 columns from different tables using measure

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

  • 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

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    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