Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Appreciate any help with this logic problem.
I have three tables
1 - MEMBERS
ID | FirstName | LastName | ...
81 Joe Smith
22 Jane Doe
31 Bob Jones
2 - EVENTS
event_id | eventDate | eventDiscipline | ....
998 01/01/2017 Yoga
999 01/02/2017 Boxing
1000 01/03/2017 Boxing
3 - ATTENDANCE
event_id | ID
998 81
998 22
999 22
999 31
1000 22
That tables are joined together by event_id and ID. So, in this example Joe and Jane attended Yoga on 1/1, Jane and Bob attended Boxing on 1/2. and Jane was the only attendee at boxing on 1/3.
I am trying to add a column to the MEMBERS table (or wherever it makes sense) that would tell me the discipline the person attended most often and use the discipline name as the value. So, based on the example above, it would look like this:
ID | FirstName | LastName | EventMost
81 Joe Smith Yoga
22 Jane Doe Boxing
31 Bob Jones Boxing
I'm assuming I will have to first create a colomun in the MEMBER table for each that is a count the event_id for each discipline, then search across these new columns for the highest value?
I'm new to DAX, so please forgive my ignorance in advance.
Solved! Go to Solution.
Hi @bwyker,
Based on my test you should be able to follow steps below to get your expected result.
1. Use the formulas below to create two new calculate columns in ATTENDANCE table.
eventDiscipline = RELATED(EVENTS[eventDiscipline])
Count_of_eventDiscipline = COUNTROWS ( FILTER ( ALL ( ATTENDANCE ), ATTENDANCE[ID] = EARLIER ( ATTENDANCE[ID] ) && ATTENDANCE[eventDiscipline] = EARLIER ( ATTENDANCE[eventDiscipline] ) ) )
2. Then you should be able to use the formula below to add a column to the MEMBERS table that would tell you the discipline the person attended most often and use the discipline name as the value.
MostOfenEventDiscipline = VAR maxCount = CALCULATE ( MAX ( ATTENDANCE[Count_of_eventDiscipline] ) ) RETURN CALCULATE ( FIRSTNONBLANK ( ATTENDANCE[eventDiscipline], 1 ), ATTENDANCE[Count_of_eventDiscipline] = maxCount )
Here is the sample pbix file for your reference.
Regards
Hi @bwyker,
Based on my test you should be able to follow steps below to get your expected result.
1. Use the formulas below to create two new calculate columns in ATTENDANCE table.
eventDiscipline = RELATED(EVENTS[eventDiscipline])
Count_of_eventDiscipline = COUNTROWS ( FILTER ( ALL ( ATTENDANCE ), ATTENDANCE[ID] = EARLIER ( ATTENDANCE[ID] ) && ATTENDANCE[eventDiscipline] = EARLIER ( ATTENDANCE[eventDiscipline] ) ) )
2. Then you should be able to use the formula below to add a column to the MEMBERS table that would tell you the discipline the person attended most often and use the discipline name as the value.
MostOfenEventDiscipline = VAR maxCount = CALCULATE ( MAX ( ATTENDANCE[Count_of_eventDiscipline] ) ) RETURN CALCULATE ( FIRSTNONBLANK ( ATTENDANCE[eventDiscipline], 1 ), ATTENDANCE[Count_of_eventDiscipline] = maxCount )
Here is the sample pbix file for your reference.
Regards
This worked perfectly! Thank you SO much for the quick response and the super efficient solution. Very much appreciated!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
77 | |
73 | |
58 | |
35 | |
31 |
User | Count |
---|---|
99 | |
57 | |
56 | |
46 | |
40 |