Hello,
I am working through creating a power bi report which looks at badge data. The report shows daily aggregations, percentiles, and other info and responds to a number of filters (buildings, dates and/or day of week, groupings such as department, etc.). The dataset has raw badge data which may have more than one badge per day per badge number (see below for small/vague sample)
| ACCESS_BADGE_NUMBER | BUILDING_NAME | BADGE_SCAN_DATE |
| 1 | Office X | 02/19/2020 |
| 1 | Office Y | 02/20/2020 |
| 1 | Office X | 02/21/2020 |
| 1 | Office X | 02/21/2020 |
| 1 | Office Y | 02/21/2020 |
| 1 | Office X | 02/24/2020 |
One graph I am trying to add in looks at badges per week. Essentially, I want to distribute at a weekly level per badge - how many days of the week did someone badge in?
I created an unique ID that looks at year + week + badge number and was able to create a measure of DISTINCTCOUNT(Date) to get what I wanted when paired with the unique ID in a table vizualization. My issue however, is that when I change the viz to a donut graph, I want to break out the categories by the days per week values (and count the number of unique IDs with the corresponding days per week).
My understanding is that you cannot put measures as the legend/detail of a donut graph, so i tried to create a calculated column to count days per week. I got this to work, but my issue with this is the column will not react to date or day of week slicers (my understanding is this is expected with calculated columns due to how its loaded in with the data). See below for details :
Is there a workaround for this? The below is essentially the goal : the graph showing the count of unique IDs with the categories being the number of days per week as well with the ability for the count of days per week to react on filters (ex. If only monday & tuesday were selected for the day of week, I would expect the number of days per week to be, at most, 2)
