cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
benswift Regular Visitor
Regular Visitor

Group Staff by count of dates and visualize so filter context is applied dynamically

I have a data table (Checks) containing StaffIDs and one row per check carried out on the quality of their work. In the table have StaffID, CheckDate, CheckType (intervention or assessment check), and ObserverRole ("MC" or "IC"). Staff can have multiple checks on multiple dates with the same or different CheckType or ObserverRole or on the same date. (The unique ID is CheckID.)

 

I need to group Staff according to the number of unique check dates (UCDs), which I can do with a measure: CALCULATE(DISTINCTCOUNT(table[CheckDate]). This measure works fine in a table or matrix (next to StaffID) and behaves as expected based on slicer selections (CheckDate, CheckType, ObserverRole).

 

BUT I want to visualize the groups of Staff in a chart by number of UCDs with total Staff on the Y-Axis and UCDs on the X-Axis. It should show 1, 2, 3, 4, 5, 6, 7, 8, etc. along the X-Axis (total UCDs) and number of Staff on the Y-Axis, i.e., with each bar representing the number of unique tutors with the number of UCDs listed on the X-axis. The idea is to allow users to click on the chart bars to filter a table on the same page and focus on a subset of Staff (e.g., those with too few UCDs). The max number of UCDs is probably around 30.

 

The problem: (a) I can create a SUMMARIZE or other table related to Checks (on StaffID) with unique staff and calculated columns containing the count of UCDs, etc., but, being calculated columns which aggregate values for Staff, these columns can't logically be filtered by my slicers; (b) I don't know how else to bucket the Staff according to number of UCDs in any other way that can be visualized since measures cannot be used in the chart Axis.

 

Basically I need to visualize Staff grouped by UCDs, but in a way that responds dynamically to filter context.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Group Staff by count of dates and visualize so filter context is applied dynamically

@benswift,

 

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Aggregates-of-counts-over-time/td-p/281500

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Group Staff by count of dates and visualize so filter context is applied dynamically

@benswift,

 

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Aggregates-of-counts-over-time/td-p/281500

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 4,107 guests
Please welcome our newest community members: