Summarizing the Attendee roles met in meetings by a manager

[Anonymous]

Appointments Table

AppointmentID	Subject	Start Date	Attendees Name	AttendeesID	AttendeesID Roles
1234-1111	D365 Meeting	11/03/2021	ATTENDEE_1	A001	Manager
1234-1111	D365 Meeting	11/03/2021	ATTENDEE_2	A002	Business Analyst
1234-1111	D365 Meeting	11/03/2021	ATTENDEE_3	A003	Developer, Team Leader
1234-1111	D365 Meeting	11/03/2021	ATTENDEE_4	A004	Functional Consultant
1234-1111	D365 Meeting	11/03/2021	ATTENDEE_5	A005	Technical Consultant, Team Leader
4567-0000	Power BI Meeting	16/03/2021	ATTENDEE_1	A001	Manager
4567-0000	Power BI Meeting	16/03/2021	ATTENDEE_2	A002	Business Analyst
4567-0000	Power BI Meeting	16/03/2021	ATTENDEE_3	A003	Developer, Team Leader
4567-0000	Power BI Meeting	16/03/2021	ATTENDEE_6	A006	Project Manager
4567-0000	Power BI Meeting	16/03/2021	ATTENDEE_7	A007	Functional Consultant
9822-2222	Power Automate Meeting	21/03/2021	ATTENDEE_8	A008	Manager
9822-2222	Power Automate Meeting	21/03/2021	ATTENDEE_6	A006	Project Manager
9822-2222	Power Automate Meeting	21/03/2021	ATTENDEE_7	A007	Functional Consultant

Hi All

From the above Appointments table if a manager attends a few meetings, I want to get a summary of all AttendeeID Roles the manager has met.

For example, if I selected 'Attendee_1' (who is a manager) from a drop down list, I want to summarize in visualizations as below

Number of 'Business Analyst' met -    2

Number of  'Team leaders' met -    2

Number of  'Project Managers' met -    1

Please, can you kindly advise how this is possible through Power BI?

Thanks

Ayyappa

[Vera_33 · Accepted solution]

Hi @Anonymous 

I added a column in M, you can use DAX calculated column as well, to specify your Roles

Table.AddColumn(#"Changed Type", "Role", each [a=Text.AfterDelimiter([AttendeesID Roles],", "),
b=if a = "" then [AttendeesID Roles] else a][b])

then a disconnected table contains distict roles only, and a measure to count (exclude the current attendee)

Count = 
VAR CurAttendee = SELECTEDVALUE(Appointment[AttendeesID])
VAR CurRole = SELECTEDVALUE('Table 2'[Role])
VAR Meeting =SELECTCOLUMNS( FILTER(Appointment,[AttendeesID]=CurAttendee),"meetingID",[AppointmentID])
VAR T1 = GROUPBY(FILTER(ALL(Appointment),Appointment[AppointmentID] IN Meeting && Appointment[AttendeesID]<>CurAttendee ),Appointment[AttendeesID],Appointment[Role])
RETURN
COUNTROWS(FILTER(T1, Appointment[Role]=CurRole))

[Anonymous]

Hi @Vera_33 

Thank you very much for your help. This was very useful. 

I tried to mimic what you did..

Created 'Table 2' to contain the RoleID and Roles. Added the relationship between 'Appointment' table and 'Table 2'

Also created a 'Count' measure as you did.

When I placed my visualizations, I am getting the 'Count' as 'Blank'

I have added my screenshots..please can you kindly have a quick look to see where I am going wrong.

Many thanks

Appointment

Table 2

Relationship

Visualization and Count Measure

[Vera_33]

Hi @Anonymous 

I was using a disconnected table2...so no relationship between the two tables. Have a try