Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
you can transpose the table by unpivot. pls see the attachment below
Proud to be a Super User!
you can transpose the table by unpivot. pls see the attachment below
Proud to be a 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])))
Thanks for your help !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |