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

[DorienM]

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?

[VahidDM · Accepted solution]

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.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[DorienM]

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.

[smpa01 · Accepted solution]

@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

[Ashish_Mathur]

Hi,

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

[VahidDM · Accepted solution]

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.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/