Forum Discussion
aggregate daily count by category
I have 3 columns and need to have a dynamic measure of them. The format is this:
Office | employee ID | Date
________________________________________________
location 1 | 123abc | 1/1/2018 08:00 am
________________________________________________
location 1 | 123abc | 1/1/2018 08:25 am
________________________________________________
location 1 | 234def | 1/1/2018 08:00 am
________________________________________________
location 2 | 123def | 1/1/2018 08:00 am
etc...
There are 5 locations, 200+ employee ID's, and 10 + datetimes for each one. My aggregation can only count each employee once per day. I don't care how many times a day they show up, just that they show up at least once. In the table above, employee ID 123abc would have a value of 1 for the date of 1/1/2018.
Each day the employeeID shows up, it should count as a new value. The anticipated output should be an aggregation of each location's daily total count. in the table above, the output should look like this:
Office | Total
________________________
location 1 | 2
_________________________
location 2 | 1
slider - 1/1/2018
I'm using this code, and it works for a single day. However, if I try to extend my slider to a date range it becomes innaccurate because it only counts each employeeID once:
2 Replies
- AlB
Community Champion
Hi bice_cold
I think it'd be best if you use a date table. Try this:
1. Create a Date table spanning the years in your data.
2. Since you have time in your dates but it's not of interest for what you need here, create a new calculated column in your table:
DateOnly = DATEVALUE(Table1[Date])
where Table1 is the name of the table you show.
3. Create a 1-to-many unidirectional relationship between Date[Date] and and the neww column Table1[DateOnly]
4. Set Date[Date] in a slicer. You can selected the date here.
5. Set Table1[Location] in the rows of a matrix visual
6. Set this measure in values of the matrix:
Measure = DISTINCTCOUNT(Table1[employee ID])
AlBThanks for your response. Your solution looks the exact same as what I'm doing except that you removed the filter that I used:
ALLSELECTED( PT_EmployeeCalls ),VALUES(PT_EmployeeCalls[EmployeeId])I already have a date table set up and use it to key off dates. I need the calculation to be used in other measurements as well though. Do you see another way to go about this?
