cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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
Community Support

Re: count between tables

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捕获.PNG 

 

Regards,

Jimmy Tao

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: count between tables

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捕获.PNG 

 

Regards,

Jimmy Tao

View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

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

Get Ready for Power BI Dev Camp

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 Kudoed Authors