Distinctcount a column if another column matches one of value in another table's column

[vnqtrang]

Hi, 

I have 2 tables as below. I would like to distinctcount the column NAME if CODE_A = CODE_B.

TableA

Name	CODE_A
John	E333RF, 1234
Karin	R344EF, 7654
Allan	Y345FT
John	1234, E333RF
Anna	E333RF, 1234
Alex	Y345FT

TableB

CODE_B

E333RF, 1234

R344EF, 7654

Y345FT

3EFE23

A123ER

B123EF

Thank you in advance.

Tg

[Avantika-Thakur]

Hi @vnqtrang ,

Considering the fact that Table B has unique codes from Code_A column of Table A , You can try creating the below measure for returing the distinct count of names in matching cases of codes between Table A and Table B -
Measure = 

IF(SELECTEDVALUE('Table A'[CODE_A]) in VALUES('Table B'[CODE_B]),

CALCULATE(DISTINCTCOUNT('Table A'[Name]),ALLEXCEPT('Table A','Table A'[CODE_A])),0)

Hope this helps!

Please accept the solution if this answers your query.

Thanks!

Avantika

[vnqtrang]

It returns all the name without filter on the code and the result doesn't take into  acount the Slicer filters.

[Avantika-Thakur]

Which slicer filters are we using in the report?

[vnqtrang]

it's slicer filter of the page, I think the Allexcept which exludes all the filters

[Avantika-Thakur]

I am not sure, if that's the Name field which is there in the page slicer.
If yes then you can try putting the slicer field in the expression of "FILTERS" in Calculate function like I have done for Name field -

Count of Names = IF(SELECTEDVALUE('Table A'[CODE_A]) in VALUES('Table B'[CODE_B]),

CALCULATE(DISTINCTCOUNT('Table A'[Name]),ALLEXCEPT('Table A','Table A'[CODE_A]),FILTERS('Table A'[Name])),0)

[vnqtrang]

it returns always the total of name

[Avantika-Thakur]

Hi @vnqtrang ,

Can you pls share the pbix as it would be easy to understand?

[vnqtrang · Accepted solution]

I found a solution to filter on all the value in the TableB. 

Thank you for your time

[vnqtrang]

TableB is unique value of tableA[Code_A], The expecting output is all the distinct Names which has the CODE_A same as one of code B

In the code_A, the order of the codes sometimes is not the same, I would like to  consider Code_A = Code_B even the order is not same.

[Avantika-Thakur]

Hi @vnqtrang ,

How are the tables A & B related to each other and how is the sample output that you are expecting?