Count distinct items from 1 column that don't exist in another column

[yuanye0710]

Hi, I'd like to write a measure to calculate total distinct items from 1 column that not found in another column. 2 columns are from different tables.

I was able to do this my adding a calculated helper column, but just wondering if there is any way to do it without adding an extra column.

For example:

Column A from table 1

-----------

AA

AA

AA

BB

CC

CC

DD

DD

EE

Column B from table 2

-----------

AA

AA

BB

BB

FF

I want to count distinct items from Column A not found in Column B.

There are 5 items in Column A not found in Column B:  

-----------

AA

AA

AA

BB

CC

CC

DD

DD

EE

And there are 3 distinct counts: CC DD and EE, so the measure should return 3.

Thanks!

[amitchandak · Accepted solution]

@yuanye0710 , except can help

In A not in B

a new table = except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) )

measure

a new table = Countrows(except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) ) )

[yuanye0710]

Thanks a lot! it's working for me!