How do I count parent records that have certain children?

[Projects4fun]

I am totally stumped. I've tried Google and ChatGPT to no avail. I know how to do this in SQL but obviously this is much different.

Any ways, I have four tables:

• Event
• Party
• Gift
• Users

The relationships are as follows:

• Event > Party is a one-to-many
• Party > Gift is a one-to-many
• Users > Gift is a one-to-many

I am trying to answer this question:

What percentage of events has each user (that joined 6+ months ago) attended since creating their account? I am trying to do this by counting the number of events where a gift exists from the user divided by the total number of events that have taken place since the user created their account.

Here's how I can produce that data using SQL:

https://goonlinetools.com/snapshot/code/#vrb01h9hmjnxcw8bnpblbs

The goal is to have a chart that shows:

• 10% of users participate in 50% of events
• 20% of users participate in 40% of events
• 30% of users participate in 30% of events
• etc.

Thanks so much.

[RossEdwards · Accepted solution]

I would think you'd want to start with a measure that says "What percentage of events does this user participate in". Then use that measure to either store the result in your User table or use it in another measure.
I was thinking something like:

Participation = var UserStartDate = MIN('Users'[JoinDate])
var GiftsCount = COUNTROWS('Gifts')
var EventsTotal = CALCULATE(
    COUNTROWS('Events'),
    FILTER(
        ALL('Events'),
        'Event'[Event Date] >= UserStartDate
    )
)
RETURN
DIVIDE(GiftsCount, EventsTotal)

On a visual you could use this measure in a filter to show all users that have X% or more.  You could get an averege % using AVERAGEX like this:

Average Participation = AVERAGEX(
    VALUES('Users'[UserID]),
    [Participation]
)

[Projects4fun]

Thanks for your help. This put me on the right track and eventually got me to:

Participation = var UserStartDate = 'app users'[created_at]
var EventsWithGifts = CALCULATE(
    COUNTROWS('app events'),
    FILTER(
        ALL('app events'),
        COUNTROWS(RELATEDTABLE('app gifts')) && NOT(ISBLANK('app events'[completed_at]))
    )
)
var totalEvents = CALCULATE(
    COUNTROWS('app events'),
    FILTER(
        ALL('app events'),
        'app events'[date] > 'app users'[created_at] && NOT(ISBLANK('app events'[completed_at]))
    )
)
var participationRate = EventsWithGifts / totalEvents
RETURN IF(ISBLANK(participationRate), 0, participationRate)