Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |