cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
byronp
Frequent Visitor

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

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 FullNamePsychologistOccupationalTherapistOTAssistSpeechTherapistSpeechAssistBehaviourSupportCoordinator
John Smith  Martin StaffClaudia Staff  Zoe Staff  
Karen SmithMark StaffMartin Staff  Gabrielle Staff Lesley Staff 
Ben Smith  Lisa Staff     Deborah Staff 
Trey Smith  Rebecca Staff     Deborah Staff 
Jordan Smith       Linda Staff 
Brian Smith Luke StaffMartin 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 StaffRebecca Staff    Susan Staff  

 

What i would like is for the the 

 

Report to look something like this 

byronp_2-1638765611323.png

 

 

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

byronp_3-1638765667117.png

 

 

 

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.  

 

byronp_4-1638766197968.png

 

 

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 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@byronp 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@byronp 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

amitchandak
Super User
Super User

@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])))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks for your help !

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors