Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bice_cold
Frequent Visitor

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:

 

CALCULATE(
    DISTINCTCOUNT(
    PT_EmployeeCalls[EmployeeId]),
        ALLSELECTED( PT_EmployeeCalls ),
        VALUES(PT_EmployeeCalls[EmployeeId])
)
2 REPLIES 2
AlB
Super User
Super User

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
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.