Measure to count values in relational tables

[sharepointguy21]

I have 3 tables below

Table 1

ID	Email
1	abc@contoso.com
2	xyz@contoso.com
3	test@contoso.com
4	123@contoso.com
5	321@contoso.com

Table 2

ID	Email
1	abc@contoso.com
2	321@contoso.com

Table 3

ID	Email
1	123@contoso.com
2	aaa@contoso.com

Tables 2 and 3 have a relationship with Table 1 on email. I want to create a measure to get the count of emails from Table 1 which have matching emails in Table 2 and Table 3. In the above scenario, the measure should return 3.

[lukiz84 · Accepted solution]

Hi,

MailCount =
   CALCULATE(
      COUNT('Table 1'[Email]),
      'Table 1'[Email] IN VALUES('Table 2'[Email]) ||
      'Table 1'[Email] IN VALUES('Table 3'[Email])
   )