count between tables

Question

I have a data set where I have two tables, sites and clients. Each client can have several sites and each site can have several different contacts (on the sites table)&nbsp;What I am trying to do is to make a list where I have a column for client, a column for site and a column for contact. I can do this bit... but what I'm struggling with is being able to then place a colum which counts the number of sites for the client and the number of contacts for the site, and for the client.&nbsp;Example:Client&nbsp;&nbsp;&nbsp;&nbsp;-&nbsp; Site&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;Contact&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp; Count sites&nbsp; - Count contacts for site - Count contact for clientClient A - Site A1 - Contact A1 - 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp; 3&nbsp;Client A - Site A1 - Contact A2 - 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp; 3Client A - Site A2 - Contact A3 - 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp; 3Client B - Site B1 - Contact B1 -&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - 2Client B - Site B2 - Contact B2 - 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp; 2

v-yuta-msft · Accepted Answer

Hi&nbsp;Back2Basics,
&nbsp;
To achieve your requirement, create three calculate columns using DAX formula like this:
Count Sites = CALCULATE(DISTINCTCOUNT(Table1[Site]), FILTER(Table1, Table1[Client] = EARLIER(Table1[Client])))

Count contacts for site = CALCULATE(DISTINCTCOUNT(Table1[Contact]), FILTER(Table1, Table1[Site] = EARLIER(Table1[Site])))

Count contact for client = CALCULATE(DISTINCTCOUNT(Table1[Contact]), FILTER(Table1, Table1[Client] = EARLIER(Table1[Client])))
The result is as below and you can refer to PBIX file here: https://www.dropbox.com/s/a9l74rbc99abw7m/count%20between%20tables.pbix?dl=0&nbsp;
&nbsp;
Regards,
Jimmy Tao

Forum Discussion

count between tables

1 Reply

Related content

Counting nulls

How to get Count from two different table- Un-match Count Table Record with New Table

Count Of Drivers

How to Count from two different table Un-matched record with New table

Table visual rows count