How to create a new table from multiple columns and then relate it back to the same table?

[byronp]

Hi All, 

I am an admin of a small clinical practice. 

I would like to build a report for all my staff that will count clients i have using a specific therapy and then a count how many clients per staff member with the use of a silcer. 

The issue is that the table is made using the Client name as the unique key and the clinicians names can appear across multiple fields.  I want to collate all my clinicians names and then relate it back to the table and use this as a slicer to to have a unique list of staff names and show counts etc . 

This is an example of the client table:

Client FullName	Psychologist	OccupationalTherapist	OTAssist	SpeechTherapist	SpeechAssist	Behaviour	SupportCoordinator
John Smith		Martin Staff	Claudia Staff			Zoe Staff
Karen Smith	Mark Staff	Martin Staff			Gabrielle Staff		Lesley Staff
Ben Smith		Lisa Staff					Deborah Staff
Trey Smith		Rebecca Staff					Deborah Staff
Jordan Smith							Linda Staff
Brian Smith	Luke Staff	Martin Staff		Lorayne Staff
Cameron Smith	Mark Staff	Martin Staff					Lesley Staff
Justine Smith	Vivien Staff	Lisa Staff				Shanna Staff
Joanna Smith	Vivien Staff					Kristina Staff	Linda Staff
Katie Smith			Claudia Staff	Lorayne Staff
Ryan Smith
Ava Smith	Mark Staff	Rebecca Staff				Susan Staff

What i would like is for the the 

Report to look something like this 

And then obviously when i click on the silcer for the staff it adjusts the counts e.g

I have created a table of unique staff names by removing the client name then unpivoting the table and then removing duplicates. This gives me a staff list. 

But now the problem is relating this staff table back to the client table. If i use a relationship from the staff list to the client table then i can only create one active relationship. This works only for one of my count cards.  

How can i successfully create a list of unique staff names from my client table then relate this back to the client table and have this as a silcer to count the client table ?

Please help, any help would be great 

Cheers, 

Byron 

[ryan_mayu · Accepted solution]

@byronp 

you can transpose the table by unpivot. pls see the attachment below

[amitchandak]

@byronp , I think you do need a table,

A measure like this should work

CountRows(distinct(union(all(Table[Psychologist]),all(Table[OccupationalTherapist),all(Table[OTAssist),all(Table[SpeechTherapist),all(Table[SpeechAssist),all(Table[Behaviour),all(Table[SupportCoordinator])))

[byronp]

Thanks for your help !