count between tables

[Back2Basics]

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)

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.

Example:

Client    -  Site      -  Contact     -  Count sites  - Count contacts for site - Count contact for client

Client A - Site A1 - Contact A1 - 2                  -  2                                   -   3 

Client A - Site A1 - Contact A2 - 2                  -  2                                   -   3

Client A - Site A2 - Contact A3 - 2                  -  1                                   -   3

Client B - Site B1 - Contact B1 -  2                  - 1                                    - 2

Client B - Site B2 - Contact B2 - 2                   - 1                                    -  2

[v-yuta-msft · Accepted solution]

Hi Back2Basics,

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 

Regards,

Jimmy Tao