cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

View solution in original post

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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and 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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 100 members 1,338 guests
Please welcome our newest community members: