cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Getting a count of accounts that have a contact that has made a purchase.

I am not too sure how to title this post, I think its best to explain with an example. I have three tables, a list of purchases, a list contacts, and a list of accounts. The relationships look like this:

Purchases * ------ 1 Contact

Contact * ------ 1 Account

Purchases:

 PurchaseID ContactID PurchaseTime 1 8 1/12/2022 2 21 1/8/2022

Contacts:

 ContactID AccountID FirstName 8 4 Joe 44 9 Samantha

Accounts:

 AccountID AccountName 4 Lobby Central Inc. 9 James & James LLC

I need to get a count of accounts that have a contact that has made a purchase. In this case the measure will return 1 because only Lobby Central Inc. has a contact that has made a purchase. Can anyone give me a hand here?

2 ACCEPTED SOLUTIONS
Super User

HI @DorienM

Try this measure:

``````Count =
CALCULATE (
COUNTROWS ( Accounts ),
FILTER (
Accounts,
Accounts[AccountID]
IN CALCULATETABLE (
VALUES ( Contacts[AccountID] ),
FILTER ( Contacts, Contacts[ContactID] IN VALUES ( Purchases[ContactID] ) )
)
)
)``````

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Super User

@DorienM  try out this measure

``````Measure =
CALCULATE ( CALCULATE ( COUNT ( Accounts[AccountID] ), Contacts ), Purchases )

//CALCULATE ( COUNT ( Accounts[AccountID] ), Contacts ) - filters Accounts that are in Contacts
//CALCULATE ( CALCULATE ( COUNT ( Accounts[AccountID] ), Contacts ), Purchases )
//further filters previously calculated {Accounts that are in Contacts} by only candiates
// are also in Purchases
//the above syntax is equivalent to Accounts INNER JOIIN Contacts INNER JOIN Purchases

``````

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
4 REPLIES 4
Helper II

Thank you everyone for your responses. We managed to find a method that worked thanks to your help.

@Ashish_Mathur To answer your question I am just looking for a count of accounts in this case but I would be interested in any sort of nuance or insight you can provide regarding the latter case as I have encountered similar challenges before.

@smpa01 @VahidDM Thank you both, you have provided valuable answers, both of which will be going in my tool box.

Super User

@DorienM  try out this measure

``````Measure =
CALCULATE ( CALCULATE ( COUNT ( Accounts[AccountID] ), Contacts ), Purchases )

//CALCULATE ( COUNT ( Accounts[AccountID] ), Contacts ) - filters Accounts that are in Contacts
//CALCULATE ( CALCULATE ( COUNT ( Accounts[AccountID] ), Contacts ), Purchases )
//further filters previously calculated {Accounts that are in Contacts} by only candiates
// are also in Purchases
//the above syntax is equivalent to Accounts INNER JOIIN Contacts INNER JOIN Purchases

``````

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Super User

Hi,

Do you only want the count of Account or do you also want the Account name and Contact name?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

HI @DorienM

Try this measure:

``````Count =
CALCULATE (
COUNTROWS ( Accounts ),
FILTER (
Accounts,
Accounts[AccountID]
IN CALCULATETABLE (
VALUES ( Contacts[AccountID] ),
FILTER ( Contacts, Contacts[ContactID] IN VALUES ( Purchases[ContactID] ) )
)
)
)``````

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors