Calculate distinct number of names from a row based on multiple dimension values

[andymanu]

Hi All,

I need to calculate the number of staff allocated to each sessions (based on the unique "Room" and the "Session_Start" combination).

Please find the below base data set,

Trainee No	Trainee Name	Staff 1	Staff 2	Staff 3	Room	Session_Start	Date
100	Mark	Matt			Room-01	2/10/2018 14:30	2/10/2018
101	Anton	Sylan			Room-02	3/10/2018 10:30	3/10/2018
102	Andrew	Matt	Sylan		Room-01	2/10/2018 14:30	2/10/2018
103	Ash	Scod			Room-03	5/10/2018 10:30	5/10/2018
104	Prish	Scod	Brenden		Room-01	12/10/2018 14:30	12/10/2018
105	David	Matt	Sylan	Brenden	Room-01	2/10/2018 10:30	2/10/2018
101	Anton	Sylan			Room-03	5/10/2018 10:30	5/10/2018
103	Ash	Scod	Brenden		Room-01	2/10/2018 14:30	2/10/2018
105	David	Scod			Room-01	16/10/2018 14:30	16/10/2018
101	Anton	Paul			Room-01	2/10/2018 10:30	2/10/2018
100	Mark	Scod	Brenden		Room-03	5/10/2018 10:30	5/10/2018
101	Anton	Sylan			Room-01	16/10/2018 14:30	16/10/2018
105	David	Scod	Sylan		Room-01	10/10/2018 14:30	10/10/2018

As depicted above,  each session will have number of Trainee Staff and assigned Staff members for the training programs.

My requirement is to calculate the total number of allocated staff for each session (considering the unique key as the combination of "Room" and "Session_Start") and construct an outcome table as shown below;

Session	Session_Start	Number of Staff
Room-01	2/10/2018 10:30	4
Room-01	2/10/2018 14:30	4
Room-01	10/10/2018 14:30	2
Room-01	12/10/2018 14:30	2
Room-01	16/10/2018 14:30	2
Room-02	3/10/2018 10:30	1
Room-03	5/10/2018 10:30	3

Appreciate, if someone could help me in this regards. 

Note

I can perform the data load part and the grouping of the "Trainee No" based on the "Room" and the "Session_Start" field but not so sure how to use the SET analysis to accomplish the above requirement using an expression.

Look forward to hearing from you.

Thank you in advance.

Kind regards,

Andy

[tresesco]

If you could use crosstable() and transform your table, set analysis might not be required. PFA

CrossTable(StaffType, StaffName, 3)
LOAD Room,
Session_Start,
Date,
[Trainee Name],
[Staff 1],
[Staff 2],
[Staff 3]

FROM
[Staff Allocation.xlsx]
(ooxml, embedded labels, table is [Group Activity]);

[andymanu]

Hi Tresesco,
Thanks for your reply.
Actually, I can't convert my straight table in to a cross table cos I got some other fields as well.
Additionally, I am also adding few fields using ApplyMap function.
Thus, appreciate if I could use an expression may be using set analysis and get the desired outcome.
Look forward to hearing from you.
Thanks.
Kind regards,
Andy

[tresesco · Accepted solution]

Try like:

RangeSum(count(DISTINCT [Trainee No]),count(DISTINCT [Staff 1]),
Count(DISTINCT [Staff 2]), Count(DISTINCT [Staff 3]))

[andymanu]

Hi Tresesco,
Thank you very much.
Both methods worked fine and I only had to change the qualifier field number.
Also, only had to remove the Count(DISTINCT [Trainee No]) from the range expression.
Thank you a lot once again.
Kind regards,
Andrew