Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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.
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.
Proud to be a Super User! | |
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.
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 |
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
141 | |
114 | |
111 | |
59 | |
59 |