Count number of times all names appear in multiple columns

[GB74]

I have a table that contains the following columns (plus some others):

I need to produce a report that displays how many meetings each person attended each week, including the ones where they were the host. Something like this:

I know how to sumarise for the week but I can't work out how to count where the name can appear in any of the 4 columns (Host, Attendee #1, 2 or 3).

I can't just create a separate column per person or something like that as in my real life scenario, I have too many people to include and we are currently experiencing high staff turnover, so I can't be be constantly editing the formulas/columns when a person leaves or a new person starts. 

Any assistance would be greatly appreciated.

[v-junyant-msft]

Hi @GB74 ,

If you are ok with data structure changes, then I think the Idrissshatila's approach should be the easiest.
But if you need to keep the current data structure, that might be tricky. I created some sample data myself:

And I added such a table Week (I'm not sure if this is convenient for you, but it would be much simpler to have such a table):

Then use this DAX to create a calculated column in Table Week:

ParticipationCount = 
VAR WeekStart = [Week Beginning]
VAR WeekEnd = WeekStart + 7
VAR _People = [People]
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER (
                'Record',
                'Record'[Meeting Date] >= WeekStart &&
                'Record'[Meeting Date] < WeekEnd &&
                (
                    'Record'[Meeting Host] = _People ||
                    'Record'[Attendee #1] = _People ||
                    'Record'[Attendee #2] = _People ||
                    'Record'[Attendee #3] = _People
                )
            )
        )
    )

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Idrissshatila]

Hello @GB74 ,

the only way for this to work is to unpivot the columns that the names are one column and another column is attendee type so that you can count them.

so you go into Power query, click on the date column and then click on transfor tab and then upivot other columns

the result would be like this

then filter out the blank records

the final result

Lso I attached the file I did this on so you can check it.

[GB74]

Thank you for the info Idrisshatila.
I'll give it a try, but I'm not sure if that will work for me as I believe the data needs to be presented the original format to produce a number of other reports within the same app.

[vicky_]

I am going to assume that you already have the weekly date table + a table of all your Staff (called StaffName below). You can try adapt the following:

Meetings Attended = 
var AllStaffMeeting = UNION(VALUES(Table[Host]), VALUES(Table[attendee #1]), VALUES(Table[attendee #2]), VALUES(Table[attendee #3])), //... etc for however many columns you have

RETURN COUNTROWS(FILTER(AllStaffMeeting, [Host] = SELECTEDVALUE(StaffName[Staff])))

The measure would go in the values section of a matrix table, with the Users in the rows and the Week Start Date in the Columns.

 This solution is not ideal for when you have a lot of columns, but the only other solution I see is to change the structure of your data completely - i.e you would have something like: 

Staff	Meeting Date	Role
Bill	01/01/2024	Host
Bill	05/01/2024	Attendee
Fred	05/01/2024	Attendee

[GB74]

Thanks vicky_, I'll give that a try. 
I'm not keen on changing the structure of the data as there are other reports within the same app that I believe will need the data to be in the original format.