cancel
Showing results for
Did you mean:
Highlighted
Helper III

## count between tables

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: count between tables

Hi

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

Highlighted
Community Support

## Re: count between tables

Hi

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

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors