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.
