Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
DorienM
Helper II
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:

PurchaseIDContactIDPurchaseTime
181/12/2022
2211/8/2022

 

Contacts:

ContactIDAccountIDFirstName
84Joe
449Samantha

 

Accounts:

AccountIDAccountName
4Lobby Central Inc.
9James & 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
VahidDM
Super User
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:

VahidDM_0-1642034114600.png

 

 

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/

 

 

View solution in original post

smpa01
Super User
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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

4 REPLIES 4
DorienM
Helper II
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.

smpa01
Super User
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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
VahidDM
Super User
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:

VahidDM_0-1642034114600.png

 

 

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/

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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