Count and visualise the number of times an ID appears

[aniekeme]

Hello,

I have a table which has two columns, ID and Event (9 different events). Im trying to figure out the best way to calculate and visualise the number attendees that attended multiple events.

So far Ive got this calculation: 

totals = SUMX(VALUES('Get That Job'[ID]),CALCULATE(COUNT('Get That Job'[Event]))) which gives me the number of events each person has attended. But Id like to group them so it counts the number of ID's that attended 9 events, and so on.

So 20 ID's attended 9 events, 10 attendees attended 5 events, and so on.

[v-sgandrathi · Accepted solution]

Hi @aniekeme,

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used the sample data on my end and successfully implemented it.  

I am also including .pbix file for your better understanding, please have a look into it:

Hope this will help you resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[aniekeme]

Thank you all for your help!

[burakkaragoz]

Hi @aniekeme ,

You want to group attendees by the number of events they attended (e.g., “20 IDs attended 9 events”, etc.). You can do this in two steps:

1. First, create a calculated column or measure to count the number of events attended by each ID:

DAX

EventsAttended = 
CALCULATE(
    DISTINCTCOUNT('Get That Job'[Event]),
    ALLEXCEPT('Get That Job', 'Get That Job'[ID])
)

1. Next, create a summary table to count how many IDs attended each number of events:

DAX

AttendeeSummary = 
SUMMARIZE(
    VALUES('Get That Job'[ID]),
    [EventsAttended],
    "AttendeeCount", COUNTROWS(CURRENTGROUP())
)

Or, if you want to do this in a visual:

• Add [EventsAttended] to a table visual.
• Add ID to the same table and set the aggregation to “Count (Distinct)”.

This way, you’ll see for each “EventsAttended” value how many unique IDs fall into that group.

Let me know if you need a step-by-step on how to set up the visuals!
translation and formatting supported by AI

[aniekeme]

Hello,

Thanks for the quick and detaield reply. I'm not too familiar with DAX queries in Power BI so i'm not quite getting the second step.

I've created the eventsattended as a measure. However when trying to create Attendeesummary [EventsAttended] isnt recognised in the syntax. Any idea what might be causing this issue?

[v-sgandrathi · Accepted solution]

Hi @aniekeme,

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used the sample data on my end and successfully implemented it.  

I am also including .pbix file for your better understanding, please have a look into it:

Hope this will help you resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-sgandrathi]

Hi @aniekeme,

We strive to make sure every issue gets addressed completely. If the previous answer helped, that’s great, please mark it as Accepted and consider giving it a Kudos.

Still facing issues? Just reply here and we’ll be right with you.

Thanks for being a valued part of the community,

[v-sgandrathi]

Hi @aniekeme,

We wanted to follow up to see if our suggestion was helpful. Please let us know how things are progressing and if you are still encountering any issues.

If the response resolved your problem, you may mark it as the solution and give it a thumbs up to assist others in the community as well.

Thank you.

[v-sgandrathi · Accepted solution]

Hi @aniekeme,

Just a quick check-in!
Has your issue been resolved with the information we shared? We’d be delighted to help further if needed.

If the solution worked, marking it as Accepted and adding a Kudos would be much appreciated,   it helps others find valuable answers.

Thanks for your continued participation.