How to get a count of customer whose not available in another table

[SamOvermars]

Hello,

I have two list of customer available in two different tables. How can I get the count of customer that are available in Table1 but not available in Table2?

Example below

Table1	Table2
Customer	Cusomter
Cus1	Cus1
Cus2	Cus1
Cus3	Cus12
Cus4	Cus12
Cus5
Cus6

[v-cgao-msft]

Hi @SamOvermars ,

Please try this measure:

Measure = COUNTROWS(DISTINCT(EXCEPT('Table1','Table2')))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Ashish_Mathur]

Hi,

Write this calculated column formula in Table1

Available in Table2 = LOOKUPVALUE(Table2[Customer],Table2[Customer],Table1[Customer])

Write this measure

Measure = calculate(countrows(Table1),Table1[Available in Table2]=BLANK())

Hope this helps.

[ryan_mayu · Accepted solution]

@SamOvermars 

pls try this

Measure = 
VAR tbl=ADDCOLUMNS('Table 1',"check",LOOKUPVALUE(Table2[Customer],Table2[Customer],'Table 1'[Customer]))
return COUNTROWS(FILTER(tbl,[check]=""))