aggregate daily count by category

[bice_cold]

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:

CALCULATE(

    DISTINCTCOUNT(

    PT_EmployeeCalls[EmployeeId]),

        ALLSELECTED( PT_EmployeeCalls ),

        VALUES(PT_EmployeeCalls[EmployeeId])

)

[AlB]

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])

[bice_cold]

@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?