Forum Discussion
Anonymous
8 years agoNot applicable
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
Microsoft 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