Counting values which are present in two different tables

[Evarie]

Hi everyone,

I've been looking for a good hour or so now and have had no luck finding an answer to my question, so I'm sorry if this has already been answered.

I have two different data tables which contain information about two different kinds of submissions businesses might make. These tables are related to each other via a Business lookup table which contains the Business ID values as the key. What I want is to be able to count the number of businesses which have entries in both tables, as shown below:

Data Table 1

Sub type A ID	Business ID
a1	A
a2	B
a3	C
a4	D

Data Table 2

Sub type B ID	Business ID
b1	A
b2	B
b3	F
b4	G

Lookup Table 1

Business ID	Name
A	Name 1
B	Name 2
C	Name 3
D	Name 4
E	Name 5
F	Name 6
G	Name 7

At this point I want a Measure which will count the number of businesses which have made both types of submission.

The result should be 2 in this case as Businesses A and B have made both types of submission.

Ultimately the goal is to have something like a pie chart which will show the total number of businesses with slices for those who have made 'A' submissions only, those who have made both 'A' and 'B' submissions, those who have made 'B' submissions only, and those who have made no submissions at all.

[danextian · Accepted solution]

Hi @Evarie ,

I can think of getting the intersection between the business id's in tables 1 and 2 and then counting the rows

test =
VAR __SUB1 =
    VALUES ( Table1[Business ID] )
VAR __SUB2 =
    VALUES ( Table2[Business ID] )
RETURN
    COUNTROWS ( INTERSECT ( __SUB1, __SUB2 ) )

[Evarie]

You legend, thanks 🙂 today I learned about declaring variables! That will make things a lot easier in the future, haha!