Forum Discussion
Count most common string, filter based on values across tables
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.
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. :smileyhappy:
Regards
2 Replies
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. :smileyhappy:
Regards
This worked perfectly! Thank you SO much for the quick response and the super efficient solution. Very much appreciated!
